Why is my \'SELECT COUNT(*) FROM change_event\' query so slow despite using a primary index?

Slow Performance with "SELECT COUNT(*)" Query Despite Index Usage

Context:

The query "SELECT COUNT(*) FROM change_event me WHERE change_event_id > '1212281603783391';" is performing exceptionally slowly in MySQL, taking over a minute to execute. This is despite the presence of a PRIMARY index on the "change_event_id" column.

InnoDB Clustered Primary Keys:

In InnoDB, primary keys are clustered, meaning they are stored alongside the data rows in the data pages. This necessitates scanning through the potentially large row data during range scans, which can reduce performance. The "change_event" table contains a TEXT column, further increasing the width of the rows.

Optimization Techniques:

• Optimize Table: Running "OPTIMIZE TABLE" can rearrange the data pages to be sorted by the primary key. This can potentially improve the performance of range scans on clustered primary keys.
• Create an Additional Non-Primary Index: Creating a non-primary index solely on the "change_event_id" column can provide a faster way to scan for a range of values. The explain plan should be checked after creating the index to ensure its utilization.

Additional Recommendation:

• Adjust the Data Type: If the "change_event_id" column is incrementing from zero, consider changing its data type to "bigint unsigned" to avoid negative values.

The above is the detailed content of Why is my \'SELECT COUNT(*) FROM change_event\' query so slow despite using a primary index?. For more information, please follow other related articles on the PHP Chinese website!