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.
Advertisements
MySQL Unsigned INT to Signed INT – A Valid Performance For Index