Redundant Index Finder

Developed In: SQL — Contributed by: Roland Bouman

This snippet allows you to create two views.

I_S_INDEXES: lists all indexes

I_S_REDUNDANT_INDEXES: lists all redundant indexes

Roland Bouman
SQL 
  1. CREATE OR REPLACE VIEW I_S_INDEXES 
  2. AS
  3. SELECT   TABLE_SCHEMA
  4. ,        TABLE_NAME
  5. ,        INDEX_NAME
  6. ,        INDEX_TYPE
  7. ,        IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE
  8. ,        GROUP_CONCAT(
  9. 		CONCAT('`',COLUMN_NAME,'`')
  10. 		ORDER BY IF( INDEX_TYPE = 'BTREE' -- when BTREE then
  11. 			,   SEQ_IN_INDEX         -- column order is important
  12. 			,   0)                   -- else
  13. 		,        COLUMN_NAME              -- only column content
  14. 	) COLUMNS
  15. FROM     information_schema.STATISTICS
  16. GROUP BY TABLE_SCHEMA
  17. ,        TABLE_NAME
  18. ,        INDEX_NAME
  19. ,        INDEX_TYPE
  20. ,        NON_UNIQUE
  21. ;
  22.  
  23.  
  24. -- todo: add "something" to indicate whether a redundant index may be required by an Innodb foreign key
  25.  
  26. CREATE OR REPLACE VIEW
  27. I_S_REDUNDANT_INDEXES
  28. AS
  29. SELECT     l.TABLE_SCHEMA
  30. ,          l.TABLE_NAME
  31. ,          CASE
  32.                WHEN l.COLUMNS = r.COLUMNS
  33.                AND (l.IS_UNIQUE = r.IS_UNIQUE)
  34.                THEN GREATEST(l.INDEX_NAME, r.INDEX_NAME)
  35.                ELSE l.INDEX_NAME
  36.            END       REDUNDANT_INDEX_NAME
  37. ,          GROUP_CONCAT(
  38.                DISTINCT
  39.                CASE
  40.                    WHEN l.COLUMNS = r.COLUMNS
  41.                    AND (l.IS_UNIQUE = r.IS_UNIQUE)
  42.                    THEN LEAST(l.INDEX_NAME, r.INDEX_NAME)
  43.                    ELSE r.INDEX_NAME
  44.                END
  45.            )         INDEX_NAME
  46. FROM       I_S_INDEXES l
  47. INNER JOIN I_S_INDEXES r
  48. ON         l.TABLE_SCHEMA = r.TABLE_SCHEMA          -- index on the same table
  49. AND        l.TABLE_NAME   = r.TABLE_NAME
  50. AND        l.INDEX_NAME  != r.INDEX_NAME            -- but not identical
  51. AND        l.INDEX_TYPE   = r.INDEX_TYPE
  52. AND        CASE
  53.                WHEN l.COLUMNS     = r.COLUMNS       -- we require column equality
  54.                AND (l.IS_UNIQUE  = 'NO'             -- redundant if not unique
  55.                  OR l.IS_UNIQUE   = r.IS_UNIQUE)    -- or if same uniqueness as other
  56.                THEN TRUE
  57.                WHEN l.INDEX_TYPE  = 'BTREE'         -- when BTREE
  58.                AND  INSTR(r.COLUMNS, l.COLUMNS) = 1 -- and l is a prefix
  59.                AND  l.IS_UNIQUE   = 'NO'            -- redundant if not unique
  60.                THEN TRUE
  61.                ELSE FALSE
  62.            END
  63. GROUP BY   l.TABLE_SCHEMA
  64. ,          l.TABLE_NAME
  65. ,          REDUNDANT_INDEX_NAME

Current Tags

indexes  redundant indexes  duplicate indexes  oksoft 

You must be logged in to tag this tool

On May 11, 2009 @ 14:32, Shantanu Oak said:

Here is another query to find all the indexes in the database "my_database" that have a cardinality/count ratio of less than 0.1% Such indexes are probably not very useful, and should be looked at carefully to justify their existence. 

  1. SELECT TABLES.table_name, statistics.index_name, statistics.cardinality, TABLES.table_rows
  2. FROM TABLES JOIN statistics ON statistics.table_name = TABLES.table_name AND TABLES.table_schema = 'my_database'
  3. AND ((TABLES.table_rows / statistics.cardinality) > 1000)

On May 11, 2009 @ 14:27, Shantanu Oak said:

For some reason I could not create the view and hence changed the first line to create a test table as shown here... CREATE TABLE test.I_S_INDEXES

Thanks for this wonderful query.

On Oct 28, 2008 @ 16:48, Malcolm Cook said:

great. Heres a stored proc to drop them all

  1. DROP procedure IF EXISTS I_S_REDUNDANT_INDEXES_DROP;//
  2. CREATE procedure I_S_REDUNDANT_INDEXES_DROP (
  3.     p_schema_name_pattern      varchar(64) -- the  name of the schema to check
  4. ,   p_table_name_pattern       varchar(64) -- a pattern to determine which tables to check
  5. ,   p_show_alter_statements    BOOLEAN     -- whether or not to show the alter table statments that it is executing
  6. ) /*
  7.  
  8. PURPOSE: drop all redundant indexes on tables whose name match
  9. <p_table_name_pattern> in any of the schemas whose name matches
  10. pattern <p_schema_name_pattern>, first showing the ALTER statments to
  11. be executed (via a select statment) if <p_show_alter_statements>.
  12. EXAMPLE:
  13.   call I_S_REDUNDANT_INDEXES_DROP('myschema','foo_%',TRUE);
  14. */
  15. begin
  16. declare stmt varchar(1000);
  17. declare done int DEFAULT 0;
  18. declare c1 cursor FOR SELECT concat('alter table `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP INDEX `',REDUNDANT_INDEX_NAME,'`') FROM I_S_REDUNDANT_INDEXES WHERE table_schema LIKE  p_schema_name_pattern AND TABLE_NAME  LIKE p_table_name_pattern;
  19. declare continue handler FOR sqlstate '02000' SET done = 1;
  20. IF  p_show_alter_statements THEN SELECT concat('alter table `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP INDEX `',REDUNDANT_INDEX_NAME,'`') FROM I_S_REDUNDANT_INDEXES WHERE table_schema LIKE  p_schema_name_pattern AND TABLE_NAME  LIKE  p_table_name_pattern; END IF;
  21. open c1;
  22. repeat
  23. fetch c1 INTO stmt;
  24. SET @stmt=stmt;
  25. IF NOT done then
  26. prepare s FROM @stmt;
  27. execute s;
  28. deallocate prepare s;
  29. end IF;
  30. until done end repeat;
  31. close c1;
  32. end;
  33. //

But....

I wonder why you write "todo: add "something" to indicate whether a redundant index may be required by an Innodb foreign key".

Perhaps this is no longer needed on newer mysql implementations? won't the query processor recognize the existence of the other index even if the auto-created index is removed?

I read that "InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan."

and that

"The index on the foreign key is created automatically if no index on the key columns exists."

but, perhaps crucially, it goes on to say:

"This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail. "

Votes

  • Rated 5.00 out of 5
Rated 5.00 out of 5 with 1 votes cast.
You must be logged in to vote.

Watches

1 members are watching this tool
You must be logged in to track this tool.

Provide Feedback

Please note:
HTML will be purified, but we allow for a number of HTML tags so that you have the flexibility to decorate your comment text to some extent. The comments allow the following HTML tags:

strong, b, em, blockquote, a, code, pre

To put code into your comment, simply encapsulate your code with
[code language="XXX"][/code], where XXX is any common language, for instance "PHP", "SQL", "C", etc.

You must be logged in to comment