View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001640Database Workbench v6MySQL Modulepublic2018-09-17 11:122022-01-12 13:35
ReporterSvein Erling Tysvær Assigned To 
PrioritynormalSeverityfeatureReproducibilityN/A
Status newResolutionopen 
Product Version5.4.4 
Summary0001640: Finding indices that are identical to or subsets of other indices
DescriptionI think (though could be wrong) the below query finds indices that are subsets of or identical to other indices for tables in a database. Don't know if this is something anyone would appreciate being available in DBW?

WITH TMP(TABLE_NAME, INDEX_NAME) AS
( SELECT DISTINCT TABLE_NAME, INDEX_NAME
  FROM information_schema.statistics )
SELECT A.TABLE_NAME, A.INDEX_NAME,
       IF( EXISTS( SELECT *
              FROM information_schema.statistics B2
              WHERE B.TABLE_NAME = B2.TABLE_NAME
                AND B.INDEX_NAME = B2.INDEX_NAME
                AND NOT EXISTS( SELECT *
                                FROM information_schema.statistics A2
                                WHERE B2.TABLE_NAME = A2.TABLE_NAME
                                  AND B2.COLUMN_NAME = A2.COLUMN_NAME
                                  AND B2.SEQ_IN_INDEX = A2.SEQ_IN_INDEX
                                  AND A.INDEX_NAME = A2.INDEX_NAME ) ), 'is a subset of', 'is identical to' ) HowItRelates,
       B.INDEX_NAME
FROM TMP A
JOIN TMP B ON A.TABLE_NAME = B.TABLE_NAME
          AND A.INDEX_NAME <> B.INDEX_NAME
WHERE NOT EXISTS( SELECT *
                  FROM information_schema.statistics A1
                  WHERE A.TABLE_NAME = A1.TABLE_NAME
                    AND A.INDEX_NAME = A1.INDEX_NAME
                    AND NOT EXISTS( SELECT *
                                    FROM information_schema.statistics B1
                                    WHERE A1.TABLE_NAME = B1.TABLE_NAME
                                      AND A1.COLUMN_NAME = B1.COLUMN_NAME
                                      AND A1.SEQ_IN_INDEX = B1.SEQ_IN_INDEX
                                      AND B.INDEX_NAME = B1.INDEX_NAME ) )
  AND ( EXISTS( SELECT *
                FROM information_schema.statistics B2
                WHERE B.TABLE_NAME = B2.TABLE_NAME
                  AND B.INDEX_NAME = B2.INDEX_NAME
                  AND NOT EXISTS( SELECT *
                                  FROM information_schema.statistics A2
                                  WHERE B2.TABLE_NAME = A2.TABLE_NAME
                                    AND B2.COLUMN_NAME = A2.COLUMN_NAME
                                    AND B2.SEQ_IN_INDEX = A2.SEQ_IN_INDEX
                                    AND A.INDEX_NAME = A2.INDEX_NAME ) )
     OR A.INDEX_NAME < B.INDEX_NAME )
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2018-09-17 11:12 Svein Erling Tysvær New Issue
2022-01-12 13:35 Martijn Tonies Project Database Workbench v5 => Database Workbench v6