Advanced Search

Get low selectivity indexes from DB schema

/ Published in: MySQL
Save to your folder(s)
Select indexes with lowest selectivity. By Jay Pipes.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. SELECT t.TABLE_SCHEMA AS `db`, 
  2.  t.TABLE_NAME AS `table`, 
  3.  s.INDEX_NAME AS `index name`, 
  4.  s.COLUMN_NAME AS `field name`, 
  5.  s.SEQ_IN_INDEX `seq in index`, 
  6.  s2.max_columns AS `# cols`, 
  7.  s.CARDINALITY AS `card`, 
  8.  t.TABLE_ROWS AS `est rows`,
  9.  ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` 
  10.  
  11.  FROM INFORMATION_SCHEMA.STATISTICS s 
  12.     INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME 
  13.     INNER JOIN ( 
  14.      SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns 
  15.      FROM INFORMATION_SCHEMA.STATISTICS 
  16.      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 
  17.  
  18.  WHERE t.TABLE_SCHEMA != 'mysql' /* Filter out the mysql system DB */ 
  19.     AND t.TABLE_ROWS > 10 /* Only tables with some rows */ 
  20.     AND s.CARDINALITY IS NOT NULL /* Need at least one non-NULL value in the field */ 
  21.     AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* unique indexes are perfect anyway */ 
  22.  
  23.  ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME /* DESC for best non-unique indexes */ 
  24.  LIMIT 10;

URL: http://www.slideshare.net/ZendCon/sql-query-tuning-the-legend-of-drunken-query-master-presentation

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.