Mysql question...

hubb0r has asked for the wisdom of the Perl Monks concerning the following question:

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!

Comment on Mysql question...

Replies are listed 'Best First'.
Re: Mysql question... by davidj (Priest) on Feb 05, 2005 at 07:32 UTC
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	[reply]
Re^2: Mysql question... by hubb0r (Pilgrim) on Feb 05, 2005 at 15:01 UTC
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...	[reply]
Re^3: Mysql question... by aquarium (Curate) on Feb 05, 2005 at 17:28 UTC
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 the hardest line to type correctly is: stty erase ^H	[reply]
Re: Mysql question... by dbwiz (Curate) on Feb 06, 2005 at 18:36 UTC
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	[reply]
Re: Mysql question... by vek (Prior) on Feb 06, 2005 at 05:44 UTC
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. -- vek --	[reply]
Re^2: Mysql question... by hubb0r (Pilgrim) on Feb 06, 2005 at 18:15 UTC
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. :)	[reply]