MySQL Unsigned INT to Signed INT – A Valid Performance For Index


MySQL
MySQL (Photo credit: Wikipedia)

You know by making an INT type unsigned you achieve 50% improvement in your select query – can’t believe it? Yes, so do I until I tried the tip by myself.

Today I am going to talk about a simple tip on MySQL – create-tables which will improve the performance of the SELECT SQL query.

Let us consider the following table structure:

CREATE TABLE customer
(
id INT(3),
first_name CHAR(20),
last_name CHAR(20),
quantity INT(50),
amount INT(50),
zipcode INT(25),
birth_date DATETIME
)
This is the simple “create table” example isn’t? This table can hold millions of data (record) no doubt on that. But can you give me a guarantee on the performance – when I request some portion of data for a range of field ‘id’? You might say why don’t you add an “index” to the fields. Yes, exactly that will improve the performance of this table, it does increase, and by optimizing this field by assigning the ‘id‘ field an unsigned it doubles the speed of returning (with or without index and performance depends on the number of records you fetch) the results of a select query.
Just replace the above create table example with the following one:
CREATE TABLE customer (
id INT(3) unsigned,
first_name CHAR(20),
last_name CHAR(20),
quantity INT(50) unsigned,
amount INT(50) unsigned,
zipcode INT(25) unsigned,
birth_date DATETIME
) 

The above example should definitely improve your performance 2/3 of the actual performance given in the previous example. The reason lies in “unsignedattribute🙂.

MySQL has a range defined for each data-type (for ex: int, char, varchar, etc) – please see this link to understand the data-type in detail. OK, so I assume you know the details of these data-types better.

Each of these types has its own ranges defined, I am going to talk about numeric-types. “Range” – is nothing but the range values MySQL can handle for that type kind of pre-defined. We have different numeric types in MySQL int, tinyint, bit, smallint, mediumint, bool, etc. In most of our create-table we have used “int” for the auto-increment field or any field which should have a unique tiny identifier.

Generally “int” has a range of -2147483648 to 2147483647 whereas if you use “int unsigned” then the range becomes 0 to 4294967295. This is where the performance can be improved when executing a query with complex where clause. You might wonder why? Let me explain with some process-flow.

Let’s say you want to know the list of customers who have purchased an item of quantity 500 or less. Following is the query you might be used to get these results:

SELECT *
FROM customer
WHERE quantity <= 500

Cool, the above query will yield you the list of customers who have purchased an item of quantity 500 or less. Right, what is the big deal, it should return fast, but consider when you have a table with millions of records then this query might be slow in returning you the results.

Yes, that is true, you can always add an “index” to the “quantity” field and improve the performance – exactly, this should improve the performance of processing the query much better than without an “index”.

Without “unsigned”:
Process flow, since the quantity field is an “int” and you have an index of this field, MySQL will define the range as -2147483648 to 500 and it will get the result based on this range.

With “unsigned”:
Process flow, since the quantity field is an “int” with “unsigned” and you have an index of this field, MySQL will define the range as 0 to 500 and it will get the result based on this range.

Now compare the difference yourself and tell me, for sure it will improve the performance of the your query. Since we know we never store any negative (signed values) in the quantity field and the default behavior of “int” is “signed“, it’s always better to write a full-syntax while creating a table.

I hope I’ve made my point clear here. Let me know if you want me to be more specific or not clear.

Have a happy coding!

MySQL Unsigned INT to Signed INT – A Valid Performance For Index

24 thoughts on “MySQL Unsigned INT to Signed INT – A Valid Performance For Index

  1. Steven says:

    Hi friend How are You ? I like your post and i want to stumble it for my friend but i cant see your social bookmark widget in this blog. Please help me admin Thank You Steven

    1. Yes it is advisable, since auto-increment value will just increment irrespective of whether it is signed or unsigned type.

      BTW, most of the traffic-driven based site should never use auto-increment field.

      1. Amit says:

        Hi rakesh,

        I just wanted to know how effective the query will be if i use unsigned a INT type.
        Also please explain me the line

        “BTW, most of the traffic-driven based site should never use auto-increment field.”

      2. Well, that is a long story, I will try to answer in a brief, auto-increment are not safe, it is not scalable, prone to problems when you want to move data from one environment to another, etc.

  2. Mike says:

    What happens when you’re looking for Quantity > 500 instead? The logic presented implies that the signed int would perform better… is that right?

    1. No Mike you are incorrect, a field with signed integer will not improve the performance due to the range defined for signed integer (please see information from the above post). The idea is when you are applying an index for quantity (which is unsigned) field then the index is defined for the range starting from 0 instead of -2147483648.

      Hope this answers your confusion.

    1. Thanks Click, I will let the WordPress people know, sorry about that. I will what I can from my end, since these ads were dynamically integrated by WordPress and I am not sure how can I stop them from displaying.

      Thanks.

    1. Sorry to make you confusion “confused” (:)), the performance does affect when you have a large amount of data and if the field is indexed and when that column type is “int signed”, it does make a difference. If I were you I will give it a shot.

  3. K.Pfeiffer says:

    I can get no significant performance differences between signed and unsigned integers and I’m sure, there couldn’t be differences. I think this post is a hoax.
    I tried this in simple and in complex statements, without performance improvements.
    The improvements you’ve got in complex select-statements maybe have an other reason.

  4. Jason Rice says:

    I was having problems with two identical queries where one took < 0s and the other 13s. It only got worse as the amount of rows in each table increased (10000+). I finally saw that the index in the problem table was signed. Changing it made a huge difference and now both queries run in milliseconds.

  5. Laugaricio says:

    The logic doesn’t make sense to me. The speed of the query should depend on the actual number of rows of data in the table that need to be scanned. Say you have a million rows with the smallest value being 0 (i.e. no negative values). The database will not start scanning from -2147483648 but from 0! It should make no difference to the query speed if the column is signed on unsigned since that only defines the range of POSSIBLE values, not actual values.

  6. abdulla says:

    Now I should copy this page – print it – and save as an important note with my other printed notes
    Thank You Very Much

  7. Your logic makes no sense – this is not how database is processing data.
    I just did some tests on mysql version 5.6.26 – there was no performance difference between signed and unsigned integer.

    1. May be true because this test was done 5 years ago and it will be different right now. At the same time I would recommend that you collect the metrics of your processing and validate it. If you don’t mind, please share it here, which would help me to update this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s