Tackling Slow "SELECT COUNT(*)" Queries on MySQL

Your query on the change_event table, counting rows that exceed a specific change_event_id, is experiencing significant delays. But why? Let's delve into possible causes.

Unveiling InnoDB's Behavior

MySQL's InnoDB engine utilizes clustered primary keys, meaning that the primary key is stored alongside row data in data pages, rather than separate index pages. As a result, range scans, such as yours, require scanning through all potentially wide rows in data pages. This factor is exacerbated by the table's xml_diff column, a TEXT data type that further slows down processing.

Optimization Strategies

To accelerate the query, two approaches are worth considering:

• Optimize Table: This command reorganizes data pages into sorted order, potentially improving the efficiency of range scans.
• Create Additional Index: Establishing a non-primary index solely on the change_event_id column creates a copy of that column in index pages. This index can be scanned considerably faster than data pages. Verify the explain plan post-creation to confirm its utilization.

Additional Tip:

To enhance performance further, consider altering the change_event_id column to bigint unsigned. This step prevents negative values and can also streamline processing.