Limit on number of indexes

EricLynnWright
EricLynnWright used Ask the Experts™
on
Is there a rule of thumb on how many indexes one should have on a MySQL database?

I have a table with a lot of fields - trying to speed it up.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
There's no exact science to it.  You just have to be aware that any additional index slows down inserts and updates.  The way I would do this is I will list down all the queries that are run on the table and see what indexes can be used by multiple queries.  For example, if I have the following WHERE clauses:

where a=? and b=? and c=?
where a=?
where a=? and c=?

for the above a concatenated index for (a, c, b) can be used by all three queries.  Note the order of colulmns - I did that so that the 3rd query can use it too.  If the index was for (a, b, c) the third query can only the index up to column a only - it won't use it up to c because there's a "b" in the middle of the index but  the query doesn't have a value for b.

Many other factors come in such as column selectivity (a column with low number of unique values might not be useful to index) and range filters ( > , < , between) in a query - you would normally put those columns on the rightmost part of the index definition.

I highly recommend the chapter on indexing of Oreilly's High Performance MySQL on this.
theGhost_k8Database Consultant
Commented:
Ofcourse it's less about following rule of thumb and finding best solution for your scenario. Above answer surely tells you a lot... I'd just like to add a few more references that you must go through:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi!

It all comes down to your applications needs that uses the tables.
As johanntangle says this is a matter of balancing  between the queries and the inserts,updates and deletes with the size of the table (number of rows) and the table structure in mind.
A well designed index can be used by one or more queries to speed them up. However every index slows down the insert,update and delete process but it does not start to hurt your applications until your table is getting large and/or you have created to many indexes on it. And of course your applications database response time requirements.
If your table becomes large, say >5 million rows, then you should consider partitioning the table to speed up both queries and inserts,updates and delete.

Regards,
    Tomas Helgi

Author

Commented:
Thanks for all the info, guys.  I appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial