Posted By

Eloi on 06/23/09

Tagged

mysql

Versions (?)

Last Edited at 06/23/09 08:02am

Statistics

Viewed 572 times

Favorited by 1 user(s)

Related snippets

The Worst Performing Indexes

/ Published in: MySQL

This script shows the top 10 worst indexes (in terms of selectivity %) on the whole MySQL server instance. Selectivity is the percentage of distinct values in an indexed field compared to the number of records in the table.
Note that this query can take some time to complete on servers with lots of databases or lots of tables.

Jay Pipes (http://jpipes.com/)

Highlighted TextExpand

/*
SQL script to grab the worst performing indexes
in the whole server
*/
SELECT
  t.TABLE_SCHEMA AS `db`
 , t.TABLE_NAME AS `table`
 , s.INDEX_NAME AS `inde name`
 , s.COLUMN_NAME AS `field name`
 , s.SEQ_IN_INDEX `seq in index`
 , s2.max_columns AS `# cols`
 , s.CARDINALITY AS `card`
 , t.TABLE_ROWS AS `est rows`
 , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`
FROM INFORMATION_SCHEMA.STATISTICS s
 INNER JOIN INFORMATION_SCHEMA.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND s.TABLE_NAME = t.TABLE_NAME
 INNER JOIN (
  SELECT 
     TABLE_SCHEMA
   , TABLE_NAME
   , INDEX_NAME
   , MAX(SEQ_IN_INDEX) AS max_columns
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA != 'mysql'
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
 ) AS s2
 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
 AND s.TABLE_NAME = s2.TABLE_NAME
 AND s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA != 'mysql'                         /* Filter out the mysql system DB */
AND t.TABLE_ROWS > 10                                   /* Only tables with some rows */
AND s.CARDINALITY IS NOT NULL                           /* Need at least one non-NULL value in the field */
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */
ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /* Switch to `sel %` DESC for best non-unique indexes */
LIMIT 10;

URL: http://forge.mysql.com/tools/tool.php?id=85

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.