MySQL Forums
Forum List  »  InnoDB

InnoDB Vs. MyISAM and filesort
Posted by: Andrew Toussaint
Date: June 06, 2005 08:35PM

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.

Vice President Technical Services
Richardson-Shaw PTY LTD

Options: ReplyQuote

Written By
InnoDB Vs. MyISAM and filesort
June 06, 2005 08:35PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.