CODEWITHSUNDEEP
mysqlindexing
topherg
topherg

Reputation: 4303

How to set up MySQL Indexes for the most optimal results

I am presently designing a database for a project that will have 8 tables, and around 3-4,000 rows. Many of the tables have numerous non-unique fields that are used for searching for results, but not all of them are used at any one time.

So, my question is, how should I set up the indexes on the tables? Should I create 1 index for each searchable field, or should I create a single index that encompasses all the searchable fields? It is just that sometime I will be using all of the fields for searches, and sometimes, I will only be using 1 or 2 fields.

ADDITIONAL

Presently, all my tables are stored using InnoDB, but that can be changed at any point

Upvotes: 0

Views: 56

Answers (3)

Markus Winand
Markus Winand

Reputation: 8746

No short answer, but the long answer is available here: http://use-the-index-luke.com/ — A guide about SQL indexing (I'm the author)

Upvotes: 1

vogomatix
vogomatix

Reputation: 5093

3-4000 rows isn't really a significant database size. As a general rule, you should look at which fields are most regularly searched and index those. Indexing every field is overkill. Adding indexes is often something you look at or revisit later when you get some feedback on how your system responds in day to day use, or in response to the growth in database size affecting performance.

Multi-key indexes should be chosen with care.

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78591

There is no general recipe or silver bullet; only a rule of thumb: create indexes that are useful.

Which candidate indexes that are useful entirely depends on you data, and on the type of queries you run on it. Some have a single column, others have multiple columns (the order counts too, btw), it just depends on what you do…

Upvotes: 2

Related Questions