Re: Mysql question...

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

Comment on Re: Mysql question...

Replies are listed 'Best First'.
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]