MySQL Forums
Forum List  »  MyISAM

Re: Create indexes on everything?
Posted by: Rick James
Date: July 30, 2014 10:22PM

> When using myISAM, does it make sense to have at least one index on every table that includes all columns?

In general, no.

MyISAM uses the key_buffer for caching index blocks.
MyISAM assumes the operating system will cache data blocks.
One could argue that these are equally efficient.

This does point out a bit of advice -- the key_buffer_size should be big enough to handle much or all of the indexes, but not so big that it robs the OS of room to cache your data. Suggested size for key_buffer_size is no more than 20% of available RAM.

> trying to figure out how this could be made as performant as possible

You can rarely "tune your way out of a performance problem". Even if you find some tunable that helps today; you won't be able to tune it more tomorrow.
Schema design, careful choice of indexes, normalization (but not over-normalization), etc, are the things you should chase.

Many MySQL (MyISAM or InnoDB) installations are plenty performant without any extra effort. "If it ain't broke, don't fix it."

Options: ReplyQuote

Written By
July 29, 2014 02:46AM
Re: Create indexes on everything?
July 30, 2014 10:22PM

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.