Guest User

SaveTheRbtz

a guest
Sep 20th, 2009
4,608
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×