This site uses tracking cookies. By using this site, you agree to our Privacy Policy. If you don't opt in, some parts of the site might not function.
Blog
Home / Blog / Web Applications / MySQL Optimization: Faster Selects with MyISAM fixed row format
06Sep 2012

MySQL Optimization: Faster Selects with MyISAM fixed row format

About the Author

Steve Bennett Steve Bennett

Steve is a Senior Technical Project Lead for Soliant in Chicago. Steve is certified in some of Soliant's key areas of expertise, including PHP, Zend Framework and MySQL.

Comments (4)

Me - January 30, 2013

Excellent post. Thank you!

Reply
ejmw - April 5, 2013

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!

Reply
mike dikas - October 27, 2013

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 ?

Reply
Harjit Singh Lakhan - December 12, 2013

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

Reply

Leave a Reply