InnoDB Vs. MyISAM and filesort

[Andrew Toussaint]

Hi all,

I have recently converted all my tables from MyISAM to InnoDB, only to find that it slowed everything down and that I had to convert them back. I know, silly approach!

Now, using a bit more of a sensible method, I am converting only one table to InnoDB. It is now easier to see that the ORDER BY part of the query is slowing it down.

I thought that the engine type would have no bearing on the sort of the data, but, when I remove the ORDER BY from the query, it is super quick regardless of engine.

Doing an EXPLAIN on the query shows that it uses filesort. I know a filesort is slow to begin with and I have read that adding an index may mean that filesort will not be used, but an index cannot be added as it is generated data (eg. distance from another person).

When I run the exact same query on the table and the engine type is MyISAM, the query is approximately 8 times faster.

My question is this: Has anyone had similiar things happen when converting tables from MyISAM to InnoDB? If so, does any one have any suggestions on how to make the sort quicker when the table type is InnoDB?

Here are some of the variables that I have set:

set-variable = innodb_buffer_pool_size=128M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M

Thanks in advance.

Drew
Vice President Technical Services
Richardson-Shaw PTY LTD