DROP TABLE IF EXISTS `QUERY_INFO`;
CREATE TABLE IF NOT EXISTS `QUERY_INFO` (
  `id` int(11) NOT NULL auto_increment,
  `SiteCode` varchar(8) NOT NULL default '',
  `UserID` varchar(30) NOT NULL default '',
  `QueryString` text NOT NULL,
  `Duration` float default NULL,
  `DTS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `DTS` (`DTS`),
  KEY `Duration` (`Duration`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

<?php

################################################################################
# index_check.php script
# ------------------------------------------------------------------------------
# Analyses query log file to see how many indexes were used during the query
################################################################################

$host			=$argv[1];
$uname			=$argv[2];
$password		=$argv[3];
$database		=$argv[4];

################################################################################
# Connect to the database
$mycon=mysql_connect($host, $uname, $password);
$mydb=mysql_select_db($database);
if ( ! $mydb ) {
	print "No database found of that name";
	die();
}
# Print out a header row for the analysis data
print "Table Name,Index,Used,QueryNo\n";

################################################################################
# pull the queries we want to analyse
$strSQL = "SELECT id, QueryString
			FROM QUERY_INFO
			WHERE QueryString REGEXP '^select'
			";
$qryResult = mysql_query($strSQL);
while ( $qryRow = mysql_fetch_array($qryResult) ) {
	# Now run the select query, adding EXPLAIN to the beginning...
	$explSQL = "EXPLAIN " . $qryRow["QueryString"];
	$explResult = @mysql_query($explSQL);
	if ( $explResult ) {
		while ( $explRow = mysql_fetch_array($explResult) ) {
			if ( $explRow["possible_keys"] != "" ) {

				# Extract a list of the possible indexes in this table
				$arrKeys=explode(",", $explRow["possible_keys"]);

				# Now print out a row for each index,
				# Stating whether the index was used or not...
				foreach ( $arrKeys as $key => $value ) {
					print $explRow["table"] . ",";
					print $value . ",";
					print ( ( $explRow["key"] == $value ) ? "1" : "0" ) . ",";
					print $qryRow["id"] . "\n";
				}
			}
		}
	}
}
mysql_close();

?>

Table Name,Index,Used,QueryNo
thd,Month,0,1
TH,PRIMARY,0,7987
TH,TH_Unique,1,7987
thd,TH_id,1,7987
thd,Month,0,7987
TH,PRIMARY,0,7988
TH,TH_Unique,1,7988
thd,TH_id,1,7988
thd,Month,0,7988
TH,PRIMARY,0,12663
TH,TH_Unique,0,12663
thd,TH_id,1,12663
thd,Month,0,12663
TH,PRIMARY,0,12664
TH,TH_Unique,0,12664
thd,TH_id,1,12664
thd,Month,0,12664
TH,PRIMARY,0,12667
TH,TH_Unique,0,12667
thd,Data_Unique,1,12667
thd,Month,0,12667