2

I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this as I am just a beginner, I have an understanding of joins, subqueries, informationschema, but I am not sure how this query is calculating if the index is unused, if you can explain on what is going on in this query that would be really helpful thank you. MariaDB 10.3.28

  SELECT
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  s.INDEX_NAME,
  s.COLUMN_NAME,
  s.SEQ_IN_INDEX,
  ( SELECT MAX(SEQ_IN_INDEX)
    FROM INFORMATION_SCHEMA.STATISTICS s2
    WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s.TABLE_NAME = s2.TABLE_NAME
    AND s.INDEX_NAME = s2.INDEX_NAME
  ) AS `COLS_IN_INDEX`,
  s.CARDINALITY AS `CARD`,
  t.TABLE_ROWS AS `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
WHERE t.TABLE_SCHEMA != 'mysql'
AND t.TABLE_ROWS > 10
AND s.CARDINALITY IS NOT NULL
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME;
7
  • It might just be easier to install userstat and wait rather than trying to understand a 12 year old gist (and maybe I'm just being lazy - fair call). With that installed at least you can compare those results to the query. Keep in mind INFORMATION_SCHEMA accesses aren't free. A big query like this opens every table definition. Welcome to DBA exchange. Good first question. – danblack Mar 3 at 6:44
  • Cardinality is related to the potential efficiency of the indexes, not their actual usage. – Kondybas Mar 3 at 6:56
  • Pay attention - some unused index may become usable and even optimal after tables statistic change... – Akina Mar 3 at 6:57
  • @danblack I cannot install 'userstat' as I am told it can affect performance so I need to find some alternate ways to do this, thank you for the answer though – DB guy Mar 3 at 8:01
  • @Kondybas thank you for explaining cardinality was having trouble getting my head around that. – DB guy Mar 3 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.