Speeding up row counting in MySQL

by

So the question is

are there any techniques for speeding up these kinds of queries?

Well, not really. A column-based storage engine would probably be faster with those SELECT COUNT(*) queries but it would be less performant for pretty much any other query.

Your best bet is to maintain a summary table via triggers. It doesn’t have much overhead and the SELECT part will be instantaneous no matter how big the table. Here’s some boilerplate code:

DELIMITER //

CREATE TRIGGER ai_books AFTER INSERT ON books
FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status
//
CREATE TRIGGER ad_books AFTER DELETE ON books
FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status;
//
CREATE TRIGGER au_books AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    IF (OLD.status <> NEW.status)
    THEN
        UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status);
    END IF;
END
//

More Related Contents:

  1. How do I add indexes to MySQL tables?
  2. Is the primary key automatically indexed in MySQL?
  3. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  4. MySQL indexes – what are the best practices?
  5. a count for each join – optimisation
  6. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  7. How to see indexes for a database or table in MySQL?
  8. MySql Count cannot show 0 values
  9. Order by COUNT per value
  10. Mysql count instances of substring, then order by
  11. DOs and DONTs for Indexes [closed]
  12. MySQL performance: multiple tables vs. index on single table and partitions
  13. Mysql covering vs composite vs column index
  14. Is there any performance gain in indexing a boolean field?
  15. MYSQL Left Join COUNTS from multiple tables
  16. “SELECT COUNT(*)” is slow, even with where clause
  17. Count with IF condition in MySQL query
  18. Optimization of MySQL search using “like” and wildcards
  19. Does Order of Fields of Multi-Column Index in MySQL Matter
  20. Create an index on a huge MySQL production table without table locking
  21. MySQL – Using COUNT(*) in the WHERE clause
  22. MySQL Integer vs DateTime index
  23. Can MySQL use multiple indexes for a single query?
  24. Should I normalize my DB or not?
  25. How to fix MySql: index column size too large (Laravel migrate)
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. How to speed up sql queries ? Indexes?
  28. Would it help to add index to BIGINT column in MySQL?
  29. MySQL: how to index an “OR” clause
  30. Mysql performance on 6 million row table

Leave a Comment