0

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

Improve this question

3 Answers 3

Reset to default
2

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…

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

Comments

1

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)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.