Snippet Name: Index_Usage_Analysis_MySQL
Tags:
Created Date: 2025-06-16 13:12:50
Last Modified Date: 2025-05-31 10:32:09
Dependencies:
DBA Notes:
Sample Output:
Title: Index Usage Analysis MySQL
Author: user
I am looking at index usage in MySQL, and limiting it to a handful of specific tables for ease of review. I am using the query below, and it is identifying indexes that have a [count_star] of less than 20. am i correctly identifying index usage?
Author: ChatGPT
To accurately assess index usage in MySQL, you can query the `performance_schema.table_io_waits_summary_by_index_usage` table. Here???s a general approach using your method of filtering by a low `[count_star]` (less than 20):
```sql
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_STAR
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE COUNT_STAR < 20
AND OBJECT_SCHEMA = 'your_database_name'
AND OBJECT_NAME IN ('table1', 'table2', 'table3')
ORDER BY COUNT_STAR ASC;
```
### **Key Points:**
1. **COUNT_STAR Interpretation**:
- `COUNT_STAR` represents the total number of I/O operations using that index.
- If an index has a very low `COUNT_STAR`, it might indicate that it is underutilized and could be a candidate for removal (if no queries rely on it).
2. **Validating Findings**:
- **Check Query Execution Plans**: Run `EXPLAIN` on queries that target these tables to see if the index is actually used.
- **Monitor Over Time**: A low `COUNT_STAR` doesn???t always mean an index is unnecessary???it might be for rare but critical queries.
- **Compare Against `mysql.innodb_index_stats`**: This provides additional statistics about index distribution and cardinality.
3. **Potential Issues**:
- If the MySQL instance has been restarted recently, `performance_schema` statistics may have been reset.
- If the database has just been migrated or tables were recently altered, previous index usage won???t be reflected.
Would you like to refine this further or add more filtering?
Author: user
select
CONCAT('--Usage:',COUNT_STAR,'\n'
'DROP INDEX ',idx.INDEX_NAME,' ON ',idx.OBJECT_SCHEMA,'.',idx.OBJECT_NAME,';') AS dropcmd,
idx.*
from performance_schema.table_io_waits_summary_by_index_usage idx
where idx.object_schema = DATABASE()
-- and idx.index_name like 'IX_LI_%'
AND idx.OBJECT_NAME IN('int_sil_stats','ext_cep_appointment','ext_ndd_appointment','int_sns_publisher_log','customer','vehicle','appointment')
AND COUNT_STAR < 20
ORDER BY idx.OBJECT_SCHEMA,idx.OBJECT_NAME,idx.COUNT_STAR DESC;
Author: ChatGPT
Your query is structured well for identifying low-usage indexes and generating `DROP INDEX` statements in MySQL. However, there are a few improvements and considerations:
### **Issues & Improvements:**
1. **String Concatenation Fix:**
- Your `CONCAT` function is missing a comma between `'\n'` and `'DROP INDEX '`:
```sql
CONCAT('--Usage:', COUNT_STAR, '\n', 'DROP INDEX ', idx.INDEX_NAME,
' ON ', idx.OBJECT_SCHEMA, '.', idx.OBJECT_NAME, ';') AS dropcmd
```
- MySQL does not support multi-line strings in `CONCAT`, so `'\n'` will be ignored in many clients unless you use a proper line break.
- Alternative: Use `\n` explicitly in some MySQL clients (`\n` might not render well unless using a command-line tool).
2. **ORDERING Clarification:**
- You are sorting by `COUNT_STAR DESC`, but because your `ORDER BY` clause includes multiple columns, you may get inconsistent ordering.
- If you specifically want the least-used indexes at the bottom, ensure:
```sql
ORDER BY idx.COUNT_STAR ASC, idx.OBJECT_SCHEMA, idx.OBJECT_NAME;
```
3. **Additional Considerations:**
- Before dropping indexes, verify:
- The indexes are not required for **foreign key constraints**.
- The indexes are not used for infrequent but essential queries (e.g., reporting or periodic batch jobs).
- Running `EXPLAIN` on queries that reference these tables confirms no reliance on the index.
- You might also check `mysql.innodb_index_stats` to understand the data distribution before dropping an index.
### **Revised Query with Fixes**
```sql
SELECT
CONCAT('--Usage:', COUNT_STAR, '\nDROP INDEX ', idx.INDEX_NAME,
' ON ', idx.OBJECT_SCHEMA, '.', idx.OBJECT_NAME, ';') AS dropcmd,
idx.*
FROM performance_schema.table_io_waits_summary_by_index_usage idx
WHERE idx.OBJECT_SCHEMA = DATABASE()
AND idx.OBJECT_NAME IN ('int_sil_stats', 'ext_cep_appointment',
'ext_ndd_appointment', 'int_sns_publisher_log',
'customer', 'vehicle', 'appointment')
AND COUNT_STAR < 20
ORDER BY idx.COUNT_STAR ASC, idx.OBJECT_SCHEMA, idx.OBJECT_NAME;
```
Would you like to incorporate index size (`DATA_LENGTH` + `INDEX_LENGTH`) from `information_schema.tables` to prioritize larger, less-used indexes?
Author: user
In mySQL, would delete statements with a WHERE clause, that has a match exactly onthe column, is WHERE ID = ? modify the COUNT_STAR counts?
Author: ChatGPT
No, **DELETE statements with an exact match on the indexed column (`WHERE ID = ?`) will not modify `COUNT_STAR` in `performance_schema.table_io_waits_summary_by_index_usage`.**
### **Why?**
- `COUNT_STAR` **only tracks read operations** on an index (e.g., for SELECTs, UPDATEs, and DELETEs when they need to look up rows).
- `DELETE` queries can **use an index for lookups** (increasing `COUNT_STAR`) but **do not increase `COUNT_STAR` just because a row is deleted**.
### **When Will DELETE Modify `COUNT_STAR`?**
If the `DELETE` statement **uses an index to locate rows**, it will **increment `COUNT_STAR` for that index**:
```sql
DELETE FROM my_table WHERE indexed_column = 123;
```
- If `indexed_column` has an index, MySQL **uses the index** to find the matching row.
- This increases `COUNT_STAR` **for that index** because it performs an **indexed lookup** before deleting the row.
However, if the query **does not use an index**, `COUNT_STAR` remains unchanged:
```sql
DELETE FROM my_table WHERE non_indexed_column = 'some_value';
```
- MySQL must **scan the entire table** (full table scan, no index lookup).
- No indexed lookup = No increase in `COUNT_STAR`.
### **Edge Cases:**
1. **Deletes Using Non-Indexed Columns**
```sql
DELETE FROM my_table WHERE non_indexed_column = 'abc';
```
- If `non_indexed_column` isn???t indexed, MySQL does a full table scan.
- No index usage ??? No effect on `COUNT_STAR`.
2. **Deletes With Multiple Conditions**
```sql
DELETE FROM my_table WHERE indexed_column = 123 AND another_column = 'xyz';
```
- If `indexed_column` is indexed, MySQL will likely use it first.
- This would **increment `COUNT_STAR` for that index**.
3. **Bulk Deletes**
```sql
DELETE FROM my_table WHERE ID > 1000;
```
- If `ID` is indexed, MySQL may use **range scans** (`COUNT_STAR` increases).
- If MySQL decides to scan the whole table instead, `COUNT_STAR` may **not** increase.
### **How to Verify If DELETE Uses an Index?**
Run `EXPLAIN`:
```sql
EXPLAIN DELETE FROM my_table WHERE ID = 123;
```
- If `key` column in the output shows an index, then `COUNT_STAR` **will increase**.
- If `key` is `NULL`, then no index was used ??? `COUNT_STAR` remains unchanged.
### **Conclusion**
- If a `DELETE` uses an index to locate rows (`WHERE ID = ?` and `ID` is indexed), then **COUNT_STAR will increase**.
- If the `DELETE` results in a full table scan (no index used), `COUNT_STAR` **will not change**.
- The act of deleting rows does **not** modify `COUNT_STAR`???only the lookup operation does.
Let me know if you need a test query to verify this in your MySQL instance!