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.
Excellent post. Thank you!
Thanks for the post! I’m currently working on converting some of our main tables to use fixed row format instead of dynamic and just wanted an idea of the possible performance benefits we could see.
That said (and not to nitpick) I think you’re selling your own example short – in the last paragraph you say it’s a 44% improvement in speed, but really it takes 44% the amount of time, so that would be a 56% improvement!
i tried your method and my finding were opposite.
in dynamic row_format mode search query took (4.09 sec) so i switched db to
fixed row_format and now it takes (1 min 3.27 sec). db size was 496 mb now its 4.9 GiB.
i am now checking with PROCEDURE ANALYSE();
any idea why is this happening ?
Hi mike dikas,
most of your columns should be non variable to get the best results. If most of your columns are variable (VARCHAR, VARbinary, TEXT, BLOB) and you changed to FIXED ROW, the columns would be padded out to make them of fixed row format. Hence the huge increase in disk space.
Rgds,
Harjit