How to figure out size of Indexes in MySQL

by

Extending Vajk Hermecz’s answer.
This is how you can get all of the indexes size, in megabytes, without the PRIMARY (which is the table itself), ordered by size.

SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name="size" AND index_name != 'PRIMARY'
ORDER BY size_in_mb DESC;

More Related Contents:

  1. INSERT INTO SELECT statement with more columns which is not in another table
  2. Why does MYSQL higher LIMIT offset slow the query down?
  3. Normalization in MYSQL
  4. MySQL load NULL values from CSV data
  5. Hierarchical queries in MySQL
  6. JOIN queries vs multiple queries
  7. Calculate difference between two datetimes in MySQL
  8. What is the maximum length of data I can put in a BLOB column in MySQL?
  9. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  10. MySql: MyISAM vs. Inno DB! [closed]
  11. Getting around MySQL “Can’t reopen table” error
  12. SQLException: No suitable driver found [duplicate]
  13. MySQL 8.0.11 error connect to caching_sha2_password the specified module could not be found
  14. Using group by on two fields and count in SQL
  15. MySQL performance: multiple tables vs. index on single table and partitions
  16. Can MySQL convert a stored UTC time to local timezone?
  17. Returning a value even if no result
  18. MySQL Workbench: Error in query (1064): Syntax error near ‘VISIBLE’ at line 1
  19. MySQL CONCAT returns NULL if any field contain NULL
  20. I can not find my.cnf on my windows computer [duplicate]
  21. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  22. Node.js connect to MySQL Docker container ECONNREFUSED
  23. How to store Query Result in variable using mysql
  24. MySQL optimizing INSERT speed being slowed down because of indices
  25. group by range in mysql
  26. MySQL update statement to store ranking positions
  27. Display all tables. Describe-like functionality
  28. Best tool for synchronizing MySQL databases [closed]
  29. Failed building wheel for mysql-python
  30. How to convert a varchar column type to date type without losing the dates

Leave a Comment