Mysql question...

[hubb0r]

Fellow monkians,

I ask this question here only because I consider you all to be a vast fount of knowledge. I apologize in advance for this not being strictly perlish question, but it ties closely to much of the database programming/administration that I've been doing with perl.

I know how to get mysql to tell me about queries that do not utilize indexes, or run slower than they should, and that's all well and good. But I have a number of databases (legacy) that I'm maintaining that have almost EVERYTHING indexed. These are very large dbs, and the size of the indexes not only cause there to be disk space considerations, but it causes things like altering tables/inserts/deletions/updates to often take longer than I think they should due to indexes being updated.

So my question is this: Is there any way of profiling the normal usage of the mysql server to determine which indexes are/are not used. Even if an index is only used once a week , I may decide to keep it... I believe many are NEVER used, and simply wasting space. I'd like to be able to drop those indexes, but I can't just start dropping them and see what slows down. Rebuilding the indexes on many of these tables takes a minimum of hours if not days!

Hopefully someone has some insight?? Thanks!

[davidj]

Hey hubb0r,

One way of doing this is to use the EXPLAIN syntax. What it does is explain how MySQL would go about executing a query. The output will show you the keys thay the engine would use. You could run EXPLAIN on the set of queries that are most likely to be used on a regular basis and see if any indexes are not used. For more information, see MySQL EXPLAIN syntax.

Hope this helps,
davidj

[hubb0r]

Thanks davidj... I do understand the use of the explain syntax; I was thinking more along the lines of a switch or something that you turn on on the mysql server which would log which indexes are used... I envisioned running with this switch on for some period of time (say a week or two) and then examining the logs to see which indexes are always used, so I can know which aren't.

Most of the queries that are run against the server are dynamically generated, so it becomes difficult to pull them and do an explain on each... Especially since there are probably 50+ programs that access the database, each with 30 -50 different queries being run...

[aquarium]

check out PQA = Practical Query Analyzer There's a commercial product called Myprofiler ($49 US), which has a index tuning wizard, and shows you the best index layout

[vek]

hubb0r, for future reference, if you have any MySQL specific questions, you could do worse than to post them on the MySQL General Discussion mailing list instead of a Perl oriented forum.

[hubb0r]

Vek, You are right of course... it's just that I spend a lot of time on perlmonks and often enough questions come up regarding dbi or other db functionality, so I figured I might give it a shot. Thanks all for your responses, even though my question was off topic. :)

[dbwiz]

Your question was answered, and all is well, but I believe you could benefit from reading this inspiring post:

If the only forum you know is PM, every question look like a SoPW.

HTH