A general rule for MySQL that I've heard throughout my time as a web developer is that MyISAM tables are faster for selects than InnoDB tables. There are a number of things that factor into this that are beyond the scope of this post. However, let’s take the time to prove that statement correct before moving on.
We'll start by creating a table demo_dynamic set to InnoDB storage engine as shown below:
With the exception of a primary key index, we won’t add any indexes on the table. This will allow us to look at the raw speed of each engine.
We’ve populated our table with 5,000,000 rows.
I’ve chosen a demo_name value to search on that’s in the middle of the row set. Note that we’ve populated the demo_name column with the results from a random key generation function instead of actual names. We’ve also specified the SQL_NO_CACHE option on our query to ensure MySQL isn’t pulling a cached result. Our search took a whopping 4.32 seconds. Not great, but keep in mind we have no index on the search column.
Now lets change the engine to MyISAM and run our SELECT again.
Ok. Safe to say MyISAM IS faster for Selects. However, one disadvantage to using MyISAM tables is that MyISAM implements table level locking. That means for the 1.58 seconds it took to run our query, any additional queries were put in a queue to be processed after our lock is released. The point at which four queries attempt to access our demo_dynamic table at the same time, it would become more efficient to have used InnoDB’s row level locking. That’s assuming those four queries weren’t attempting to access the same row.
Fortunately there is a faster way for MyISAM tables to select records by using Fixed (or Static) row formats. MyISAM uses a Dynamic row format by default when a table contains any columns that are of variable length. (Ex. VARCHAR, TEXT, BLOB).
You can check the current row format of your table by looking at the INFORMATION_SCHEMA.TABLES table.
To test, we’ll create a new table demo_fixed from our demo_dynamic table and set the row format to fixed.
That’s about a 44% improvement in speed for Fixed row format over Dynamic. The disadvantage to Fixed row formats is the space required to store columns. Changing to a Fixed row format will pad any variable length columns with spaces. However, any time your trade-off for performance comes at a cost of disk space, performance wins.