Posted By

kurokikaze on 11/02/09

Tagged

Versions (?)

Last Edited at 11/02/09 07:13am

Statistics

Viewed 495 times

Favorited by 0 user(s)

Related snippets

Get low selectivity indexes from DB schema

/ Published in: MySQL

Select indexes with lowest selectivity. By Jay Pipes.

Highlighted TextExpand

SELECT t.TABLE_SCHEMA AS `db`, 
 t.TABLE_NAME AS `table`, 
 s.INDEX_NAME AS `index 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 /* unique indexes are perfect anyway */

ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME /* DESC for best non-unique indexes */ 
 LIMIT 10;

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.