SHARE
TWEET

SaveTheRbtz

a guest Sep 20th, 2009 4,219 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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;
RAW Paste Data
Top