Title:	MySQL Optimization
Author:	Travis M. Owens aka Rhelic [ travisowens@hotmail.com ]
Date:	2002/01/17

My testbed MySQL was a Sun Netra t1120 Dual 440mhz UltraSparc II Processor with 1GB RAM and you can read more at Sun.com.

INT vs TIMESTAMP

Question: Which is faster, using a unix timestamp method via INT(5) or using MySQL's built in time/date definiton via TIMESTAMP(14).  While a unix timestamp isn't human readable but it is the easiest to compare via = > < and easy to add and subtract time too.

Conclusion: INT(5) can be upto 8x (thats 800%) faster. This is by far the most extreme speed difference in anything I have ever tested in MySQL.  After seeing these results I insist you always store time as a normal numeric in a unix timestamp format (or a manually YYYYMMDDHHMMSS if you insist).  Even the smallest of databases will gain a tremendous speed improvement if using a manual time/date method since it is by far the fastest implimentation.

using SELECT * FROM myint WHERE mytime > 1011119030 on a DB of "key | INT(5)" where INT(5) represents a unix timestamp with 10,000 entries INT(5) took 0.27 seconds with 10,000 entries using SELECT * FROM mydt WHERE mytime > 20020115132530 on a DB of "key | TIMESTAMP(14)" where TIMESTAMP(14) represents YYYYMMDDHHMMSS with 10,000 entries TIMESTAMP(14) took 1.57 seconds with 10,000 entries using SELECT * FROM mydt WHERE UNIX_TIMESTAMP(mytime) > 1011119030 on a DB of "key | TIMESTAMP(14)" where TIMESTAMP(14) represents YYYYMMDDHHMMSS with 10,000 entries UNIX_TIMESTAMP() on a TIMESTAMP(14) took 0.82 seconds with 10,000 entries

CHAR vs VARCHAR

Question: Which is faster, using a string via CHAR or VARCHAR? Most people believe CHAR is faster but wastes disk space, while VARCHAR is slower but doesn't waste any space.

Conclusion: There is only a small speed difference in CHAR vs VARCHAR in a normal database, but despite public opinion VARCHAR is actually faster than CHAR. But in large databases there is a medium (at best) speed improvement in using VARCHAR.

There speed differences between CHAR and VARCHAR aren't very large. The biggest difference was found here: using SELECT * FROM mychar WHERE mychar = '9993abc...yz9993' on a DB of "key | CHAR(255)" CHAR(255) took 0.18 secs with 10,000 entries using SELECT * FROM myvarchar WHERE myvarchar = '9993abc...yz9993' on a DB of "key | VARCHAR(255)" VARCHAR(255) took 0.12 secs with 10,000 entries And when performing a LIKE = '%abcdefg%' there was almost no difference speed wise. CHAR(255) took 0.317 secs with 10,000 entries VARCHAR(255) took 0.310 secs with 10,000 entries