How can I find out if I have too many indexes on my MySQL table?
For some of the tables that need to be accessed quite a lot, I've added about 2-4 indexes on the columns that will be accessed a lot. But how can I find out if they're doing more harm than good?
The danger in having too many indexes is that they may slow down inserts/updates/deletes because the indexes need to be recalculated. Benchmark performance of inserts/updated/deletes. If you're not seeing significant measurable performance degradation with your indexes, leave them alone.
There are two techniques.
Get query execution plans for the most common queries. Check off indexes being used. All the other indexes -- not showing up in query execution plans -- are not being used.
Measure performance of selected queries. Drop an index. Measure performance again and see if it got worse or not.
You must consider Insert and Update activity as well as Select activity. Indexes make select fast, but insert slow.
Don't simply study or measure one transaction. It's the overall mix of transactions that determine the value of indexes.
In some cases, you can consider dropping some indexes to do an insert, and then rebuilding them.
There are a number of elements to this question:
Answering these depends on the control you have over your queries. Assuming you have a web application, you probably have a pretty good idea of the queries that are coming in. If not, enable the query log for a while to see what's happening.
Next, run an EXPLAIN on each of these queries and take note which indexes are being used to satisfy them. If any go unused, then you should probably remove them to save on the space and INSERT penalty.
Next, you need to see if the indexes are helping. Benchmarking is the most accurate method, but you may want to do this on a replica of your database and not on the live production copy.
Note that the only performance hits come when you are updating a table with indexes as said indexes must be maintained as the data in the table changes. If you are only selecting from a table, indexes generally can only improve performance and not degrade it. Obviously, you'll want to rid your table of any un-used indexes as they unnecessarily waste disk space and again will have to be maintained as the table changes over time.
Typically you'll want to check your execution plans to see which indexes are actually used based on your queries. Be sure to gather statistics before doing this for accurate results. Doing this check of the execution plan is important, b/c even though you have an index on a column, the optimizer may not choose it based on factors such as cardinality of the column, etc. On large tables where queries are highly selective (i.e. you have 1M users and you're query is where userid = 'x' which will return 1 row), you're going to find the index for the select is worth the cost of maintenance. Small tables, say a table of continent names, will generally not benefit much from an index as many times a full table scan will be preferred over an index scan. This is due to the fact that it will cost more to read the index and then read the data block referenced by the index as opposed to just reading the small amount of data directly. Again, these things will need to be verified based on your specific tables and needs, and it's all done by looking at the execution plans.
Favor concatenated indexes over single column indexes if your queries lend themselves to these types of queries. For example, if your where clauses typically do something like "where emp_fname = 'jim' and emp_lname = 'smith', create one concatenated index on fname and lname instead of individual indexes on each column.