MySQL Aurora slow COUNT query

Question

I have a COUNT(*) query that is really fast in several DB's, but is really slow on MySQL AURORA:

I can't seem to figure out why. First I thought it was because it went the MEMORY went over the 4MB, but I haven't been able to verify this.

I have already ruled out:

The query itself uses indexes (see the EXPLAIN), so it has to be something else.
The CPU usage is OK (this is a test DB, so there are no other queries running)

QUERY:

SELECT count(project_reports.id) as aggregate
FROM
    `project_reports`
    INNER JOIN `project_user_cache` ON `project_reports`.`project_id` = `project_user_cache`.`project_id`
WHERE
    `project_user_cache`.`user_id` = 5
    AND `project_reports`.`deleted_at` IS NULL;

EXPLAIN:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  project_reports     ref project_id,idx_deleted_date,project_reports_project_id_created_by_index idx_deleted_date    5   const   17908   100.00  Using index condition
1   SIMPLE  project_user_cache      eq_ref  idx_user_project_unique,idx_user_id,idx_project_id  idx_user_project_unique 16  const,stagingCentralDB.project_reports.project_id   1   100.00  Using where; Using index

ANALYZE:

-> Aggregate: count(project_reports.id)  (cost=26132 rows=1) (actual time=24962..24962 rows=1 loops=1)
    -> Nested loop inner join  (cost=24341 rows=17908) (actual time=1.5..24953 rows=46385 loops=1)
        -> Index lookup on project_reports using idx_deleted_date (deleted_at=NULL), with index condition: (project_reports.deleted_at is null)  (cost=5372 rows=17908) (actual time=1.47..24709 rows=66265 loops=1)
        -> Filter: (project_reports.project_id = project_user_cache.project_id)  (cost=0.959 rows=1) (actual time=0.00335..0.00342 rows=0.7 loops=66265)
            -> Single-row covering index lookup on project_user_cache using idx_user_project_unique (user_id=5, project_id=project_reports.project_id)  (cost=0.959 rows=1) (actual time=0.00308..0.00311 rows=0.7 loops=66265)

Any help would be appreciated.

user3634184 · Accepted Answer · 2025-09-23 21:16:58Z

0

I eventually fix it by running:

ANALYZE TABLE project_reports; (query from 26 seconds down to 17 seconds)
AND adding composite indexes (query from 17 seconds down to 900ms)

answered Sep 23 at 21:16

user3634184

4662 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Sep 26 at 21:00

For the benefit of others, show the original indexes and the new indexes.

Collectives™ on Stack Overflow

MySQL Aurora slow COUNT query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related