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,