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.
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 )
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 “unsigned” attribute :-).
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:
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”.
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.
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!