How many times are my indexes used?

[Hasgaroth]

Hi All,
I manage quite a large system of mySQL MYISAM databases, roughly 400 in total, each with about 100 tables - the total size of the db system is over 800Gb, with over 8 billion rows in total (including summary tables).

All the databases are in one of two formats, each with a set of tables storing a particular segment of the data.

All the tables have index built on them, but what I am wondering is how many of the indexes are actually used, and how often.

Our main concern at the moment is disk space, and making the best use of it. I would like to locate and remove any indexes that are not actually used in the querying of the data.

Most of the table structures are using the correct field definitions, tinyint and smallint instead of int where appropriate, etc, and the rest are being worked on to reduce the data file sizes.

I can find out when an index was last accessed using the timestamp on the filesystem (Linux), but what I am after is when it was last used in a query?

Knowing when it was last updated is not really relevant, as our data is updated frequently.

Hope someone out there can help!

Thanks in advance,

Hasgaroth.

[Peter]

Hi,

Yes this is hard nut to crack, especially as indexes are stored in single file for MyISAM tables which means you would not see which of indexes is used if there are any.

The way you can find unused indexes is to have general query log, run EXPLAIN on each select query in it and analyze “index” column in output to see which indexes were used by this set of queries.

This leaves out indexes used by only update/delete queries but is still pretty good in most cases.

You also can count queries and ie see how many queries used one or another index.

[Hasgaroth]

Thanks Peter,
That is pretty much what I thought I’d have to do. Thankfully, we have been storing all the major queries run on the database in a log table, including the duration for the query. This has already helped us identify where we may need to add indexes, but I think I’m going to have to write a small routine to analyse the table running an ‘EXPLAIN’ on each query and analysing the output.

When I get it done, I’ll post the script here in case it’s useful for anyone - I’ll include the schema for our query log table too.

Thanks again, and congrats for putting together such a useful forum!

Has.

[Hasgaroth]

Hi Peter,
I think it already does that - it is my understanding that the EXPLAIN command lists all the indexes in a table in the ‘possible_keys’ field, and the one chosen in the ‘key’ field.

Hopefully this will mean that all the indexes built are listed, even if they aren’t used.

[Peter]

Nope,

Possible Keys do not have to list all indexes which table has.
You would need to run SHOW CREATE TABLE or SHOW INDEX FROM to get them