Optimize MySQL Count Query

Speeding up row counting in MySQL

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
//

How can I optimise COUNT with GROUPBY mysql query?

A correlated subquery can be a fast method:

SELECT es.id, es.title,
      (select count(*)
       from eb_evaluation_user_symptom eus
       where es.id = eus.eb_evaluation_symptom_id
      ) as cnt
FROM eb_evaluation_symptom es  
WHERE es.is_active = 1 ;

For performance, you want an index on eb_evaluation_user_symptom(eb_evaluation_symptom_id).

An index on eb_evaluation_symptom won't be of much help, because that table is so small.

Optimize SQL query using `COUNT`

I like Barmar's answer, but because of the filtering on b, I suggest a small(?) change. (I rearrange the tables primarily to follow the Optimizer's likely order.)

SELECT DISTINCT a.*
FROM  (
    SELECT field1 
    FROM bbb 
    WHERE xxx = 'x' 
    GROUP BY field1 
    HAVING COUNT(*) <= 5
) AS bc
INNER JOIN bbb AS b ON b.field1 = bc.field1 
INNER JOIN aaa AS a ON a.field1 = bc.field1
WHERE b.zzz = 'zzz' 
  AND a.yyy = 'yyy'

Plus these indexes:

    bbb:  INDEX(xxx, field1)  -- this order
    b:    INDEX(field1, zzz)  -- either order
    a:    INDEX(field1, yyy)  -- either order

The first two composite indexes will be "covering".

The main change from Barmar's answer was to leave a.* for last.

If you want to discuss this Question further, it may be useful to provide SHOW CREATE TABLE and EXPLAIN.

How to optimize sql query with COUNT and GROUP BY

For this query:

SELECT c.actorId, COUNT(*)
FROM cast c
WHERE EXISTS (SELECT 1
              FROM watched w
              WHERE w.movieId = c.movieId AND w.userId = 8
             )
GROUP BY c.actorId;

You want an index on watched(movieId, userId). An index on cast(movieId, actorId) might also prove useful.

Notice that I changed the table aliases to be more meaningful than arbitrary letters.

EDIT:

Given the size of the tables, I think an explicit join might be better:

SELECT c.actorId, COUNT(*)
FROM watched w JOIN
     cast c
     ON w.movieId = c.movieId
WHERE w.userId = 8
GROUP BY c.actorId;

For this query, you want indexes on watched(userId, movieId) and cast(movieId, actorId). This version assumes you don't have duplicate rows in watched.

Optimize a count query or restructure the design?

Try rewriting the query as a correlated query:

select p.product_id, p.model, pd.name, (
    select count(*)
    from product_view as pv
    where pv.product_id = p.product_id
    and pv.date_create >= '2021-07-25'
    and pv.date_create <  '2022-03-10' + interval 1 day
) as total
from product as p
left join product_description as pd on p.product_id = pd.product_id
where exists (
    select 1
    from product_view as pv
    where pv.product_id = p.product_id
    and pv.date_create >= '2021-07-25'
    and pv.date_create <  '2022-03-10' + interval 1 day
    -- this is a far more optimized version for dates used in your op
)
order by total desc
limit 0, 20

This does not involve grouping so it should be faster than your original query. If date filter is not required then remove the where exists part and and pv.date_create ... from the count sub-query.

Secondly, I don't see any useful indexes in the explain. You should try the following indexes:

create index ix1 on product_view (product_id, date_create)
-- should be (i) good for joining (ii) "covers" the date column

How to optimize COUNT(*) performance on InnoDB by using index

For the time being I've solved the problem by using this approximation:

EXPLAIN SELECT COUNT(id) FROM data USE INDEX (PRIMARY)

The approximate number of rows can be read from the rows column of the explain plan when using InnoDB as shown above. When using MyISAM this will remain EMPTY as the table reference isbeing optimized away- so if empty fallback to traditional SELECT COUNT instead.

---

Related Topics