Advanced Search

The Worst Performing Indexes

/ Published in: MySQL
Save to your folder(s)
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/)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /*
  2. SQL script to grab the worst performing indexes
  3. in the whole server
  4. */
  5. SELECT
  6.   t.TABLE_SCHEMA AS `db`
  7.  , t.TABLE_NAME AS `table`
  8.  , s.INDEX_NAME AS `inde name`
  9.  , s.COLUMN_NAME AS `field name`
  10.  , s.SEQ_IN_INDEX `seq in index`
  11.  , s2.max_columns AS `# cols`
  12.  , s.CARDINALITY AS `card`
  13.  , t.TABLE_ROWS AS `est rows`
  14.  , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`
  15. FROM INFORMATION_SCHEMA.STATISTICS s
  16.  INNER JOIN INFORMATION_SCHEMA.TABLES t
  17.   ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  18.   AND s.TABLE_NAME = t.TABLE_NAME
  19.  INNER JOIN (
  20.   SELECT 
  21.      TABLE_SCHEMA
  22.    , TABLE_NAME
  23.    , INDEX_NAME
  24.    , MAX(SEQ_IN_INDEX) AS max_columns
  25.   FROM INFORMATION_SCHEMA.STATISTICS
  26.   WHERE TABLE_SCHEMA != 'mysql'
  27.   GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
  28.  ) AS s2
  29.  ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
  30.  AND s.TABLE_NAME = s2.TABLE_NAME
  31.  AND s.INDEX_NAME = s2.INDEX_NAME
  32. WHERE t.TABLE_SCHEMA != 'mysql'                         /* Filter out the mysql system DB */
  33. AND t.TABLE_ROWS > 10                                   /* Only tables with some rows */
  34. AND s.CARDINALITY IS NOT NULL                           /* Need at least one non-NULL value in the field */
  35. AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */
  36. ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /* Switch to `sel %` DESC for best non-unique indexes */
  37. LIMIT 10;

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.