Comparing MySQL search speed for different column types
Starting work on a new website I wanted to know which type of MySQL column was the fastest to search on. I assume it’s a numeric column, but I really want to know how much of a difference it makes. What I’m working on right now is an options table and it would be a lot easier to have options like ‘page_color’ rather than tying a constant to page color equaling 4 (for example). But if storing it using a constant makes the searches significantly faster then it’s worth it.
To test I’ve setup a database with 100,000 records with 4 fields in each record. The 4 fields are id (primary key, int, unsigned), search_num (int, unsigned), search_char (char(50)), and search_varchar (varchar(50)). A single field index was created on each of the search fields. The data was random and inserted through a script.
Each data type of searched 1,000 times and then repeated 10 times for a total of 10,000 searches. The numbers below are the average search time for the 10 1,000 search runs.
int: 0.6179 seconds
char: 0.6118 seconds
varchar: 0.6309 seconds
Again, each of these times represents 1,000 searches so the individual difference is much smaller.
So at the end it seems that int searches are a little faster but probably not enough to matter. So I’m going to go with a char field to make it easier for me to work with.
Question, Comments...
Do you have more questions. Please either leave a comment below or join us in our new forum.