Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

How do you identify unused indexes in a MySQL database?

Tags:

optimization

indexing

mysql

I have recently completely re-written a large project. In doing so, I have consolidated great number of random MySQL queries. I do remember that over the course of developing the previous codebase, I created indexes on a whim, and I'm sure there are a great number that aren't used anymore.

Is there a way to monitor MySQL's index usage to determine which indexes are being used, and which ones are not?

like image 851
Chris Henry Avatar asked Jul 14 '10 04:07

Chris Henry

People also ask

How do you check if indexes are used or not?

In Oracle SQL Developer, when you have SQL in the worksheet, there is a button "Explain Plan", you can also hit F10. After you execute Explain plan, it will show in the bottom view of SQL Developer. There is a column "OBJECT_NAME", it will tell you what index is being used.

How do you check if indexes are being used in MySQL?

Your answer To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.

How do I see all indexes in MySQL?

You can list a table's indexes with the mysqlshow -k db_name tbl_name command. In MySQL 8.0. 30 and later, SHOW INDEX includes the table's generated invisible key, if it has one, by default.

What happens if indexes are missing or not set correctly?

Indexes are one of the most important features of the SQL Server while being most overlooked and misused in practice. Their proper use leads to great server performance, but having them set up incorrectly or not having them set up at all, leads to poor execution times.

1 Answers

I don't think this information is available in a stock MySQL installation.

Percona makes tools and patches for MySQL to collect index usage data.

See:

  • User Statistics (and Index Statistics)
  • How expensive is USER_STATISTICS?
  • pt-index-usage

See also:

  • New INDEX_STATISTICS table in the information_schema
  • check-unused-keys: A tool to interact with INDEX_STATISTICS
  • New table_io_waits_summary_by_index_usage table in performance_schema in MySQL 5.6

You may also be interested in a Java app called MySQLIndexAnalyzer, which helps to find redundant indexes. But this tool doesn't have any idea which indexes are unused.

like image 148
Bill Karwin Avatar answered Sep 30 '22 16:09

Bill Karwin
Sign in to Comment

Related questions

Handling Complex WHERE clauses with a PHP Query Builder
PDO positional and named parameters as part of the same prepared query?
Mysql unique index doesn't work on a certain umlaut
Odd IntegrityError on MySQL: #1452
How to intentionally crash MySQL table?
Select parent row only if it has no children
Ruby - Exception occured: [Mysql2::Error] closed MySQL connection
Logical AND operator in mySql REGEXP?
Limit SQL by the sum of the row's value
MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause
'ER_PARSE_ERROR' on node mysql when inserting multiple values
Duplicate entry for key 'group_key'
max connection MySql reached during tests
No query results for model [App\Products] Laravel
Is there any reason to ever use TINYTEXT?
mbind: Operation not permitted when connecting with sequel pro
Which is the best way to bi-directionally synchronize dynamic data in real time using mysql
SQL "GROUP BY" issue
Is there any good universal PHP MySQL HTTP tunnel? [closed]
How many of you have gone from MySQL to Postgresql? Was it worth it?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!