Posts Tagged ‘optimization

Why use MyISAM?


So far, this has read somewhat like a paid advertisement for InnoDB. However, MyISAM has some very real advantages. One of these is the simplicity of the engine, it is very well understood and it’s easy to write third party tools to interact with it. There are very high quality free tools, such as mysql hot copy available for MyISAM. It is much more difficult to write tools for an engine as complicated as InnoDB and this can be easily seen in the number of them available. Also, this simplicity allows for an ease of administration that is not there with InnoDB.


MyISAM’s other main advantage is how long it has been around. There are many systems, Drupal for example, that are very much optimized for that particular engine. This is not to say that they perform poorly on InnoDB, but they are not optimized for it. For example,while many of Drupal’s core queries are well indexed and use the primary key (thus benefiting from InnoDB’s primary key clustering), some could be improved. The node table has a primary key on (nid, vid). Having this index is a good idea, but it is a two integer index and there are eleven secondary indexes based on it. This doesn’t mean much when you use MyISAM, but under InnoDB it means each of those secondary indexes has two integer sized leaves identifying the primary key.

Another fact, is that there are some workloads MyISAM is better suited for. For example,Drupal’s built in search functionality performs horribly on InnoDB for very large datasets, for example 100k+ rows. These tables are best left MyISAM. Fortunately, MySQL allows for mixing engines like this.

Resource Usage

It is readily accepted in computer science that there is often a trade off ¬†between speed and memory footprint. We have seen through the above benchmarks that InnoDB has some fast algorithms, however, this comes at a price. Not only does InnoDB use more memory than MyISAM, but the actual data files are often quite a bit larger. Add to this the fact that InnoDB has at least one quite large log file and you have a significant increase in resource use. This makes MyISAM a good fit for a resource limited server. However, if you’re concerned at all with high levels of concurrency, it is likely you have the funds to buy a server that can handle these increased resource demands.

Tags : , , , , , , , , ,

Guidelines for creating search arguments

Follow these guidelines when you write search arguments for your queries:

select * from table1 where int_col > 3

It is probably more efficient to write the query like this:

select * from table1 where int_col >= 4

This optimization is more difficult with character strings and floating point data. You need to know your data.


Tags : , , , , , , , , , ,