“SELECT COUNT(*)” is slow, even with where clause

by

InnoDB uses clustered primary keys, so the primary key is stored along with the row in the data pages, not in separate index pages. In order to do a range scan you still have to scan through all of the potentially wide rows in data pages; note that this table contains a TEXT column.

Two things I would try:

  1. run optimize table. This will ensure that the data pages are physically stored in sorted order. This could conceivably speed up a range scan on a clustered primary key.
  2. create an additional non-primary index on just the change_event_id column. This will store a copy of that column in index pages which be much faster to scan. After creating it, check the explain plan to make sure it’s using the new index.

(you also probably want to make the change_event_id column bigint unsigned if it’s incrementing from zero)

More Related Contents:

  1. MYSQL OR vs IN performance
  2. MySql views performance [closed]
  3. How do I add indexes to MySQL tables?
  4. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  5. How to Perform an UPSERT so that I can use both new and old values in update part
  6. JDBC batch insert performance
  7. SQL Performance UNION vs OR
  8. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  9. MySQL: Fastest way to count number of rows
  10. MySQL vs MongoDB 1000 reads
  11. Speeding up row counting in MySQL
  12. MySQL “IN” operator performance on (large?) number of values
  13. What is better in MYSQL count(*) or count(1)?
  14. DOs and DONTs for Indexes [closed]
  15. MySQL view performance
  16. MySQL: Inner join vs Where [duplicate]
  17. Higher cardinality column first in an index when involving a range?
  18. Optimization of MySQL search using “like” and wildcards
  19. Does Order of Fields of Multi-Column Index in MySQL Matter
  20. Using index, using temporary, using filesort – how to fix this?
  21. MySQL: Many tables or many databases?
  22. sql group by versus distinct
  23. Should I normalize my DB or not?
  24. is php sort better than mysql “order by”?
  25. How can “set timestamp” be a slow query?
  26. How to speed up sql queries ? Indexes?
  27. Would it help to add index to BIGINT column in MySQL?
  28. ALTER TABLE ADD COLUMN takes a long time
  29. Mysql performance on 6 million row table
  30. Composite Primary Key performance drawback in MySQL

Leave a Comment