Optimizing "SELECT COUNT(*)..." with Where Clause

The issue at hand concerns a significantly slow "SELECT COUNT(*)" query in MySQL, even when a "WHERE" clause is applied. To address this challenge, it's crucial to understand MySQL's storage mechanism.

Clustered Primary Keys

InnoDB, the storage engine used in this instance, utilizes clustered primary keys. This means that the primary key is stored alongside the data row in the same data pages, rather than in separate index pages. Consequently, performing a range scan on a clustered primary key requires scanning through all rows, including their potentially wide column values. The table in question contains a TEXT column, further exacerbating the performance issue.

Optimization Strategies

To optimize this query, consider the following strategies:

1. Optimize Table: Running "OPTIMIZE TABLE" ensures that the data pages are physically sorted in order. This optimization can potentially improve the performance of range scans on clustered primary keys.
2. Create an Additional Index: Consider creating a non-primary index solely on the "change_event_id" column. This will create a copy of that column in index pages, which is significantly faster to scan compared to the clustered primary key. Verify the explain plan after creating the index to confirm its utilization.

Additional Suggestion

To further enhance performance, consider modifying the "change_event_id" column to be "BIGINT UNSIGNED" if it increments from zero. This change can result in reduced storage requirements and improved performance.