MySQL COUNT with WHERE and HAVING and ORDER BY in the same query

Question

I am running a MySQL query -

SELECT
u.userid, u.lastopen, 
r.auto_renew_status, r.product_id, r.is_trial_period, 
(SELECT count(apd.id) FROM apple_purchase_details as apd 
  WHERE apd.id = u.userid 
  GROUP BY u.userid 
  HAVING  count(apd.id) < 5 
) as total
FROM users as u, receipt as r 
WHERE u.userid = r.id and r.is_trial_period = 'false' and r.auto_renew_status = 0

The query is working fine, But it is returning NULL where total is greater than 5.

Also when I try to add ORDER BY clause on u.lastopen it takes too long to load. I have already indexed lastopen in users table.

Can anyone help me with this?

Answer 1

It's returning NULL where total is greater than 5, becuase you specified so with HAVING count(apd.id) < 5, which means that subquery will return nothing when it's bigger than 5, resulting in NULL value for that particular row.

If you don't want that behaviour, delete HAVING constraint in subquery.

This query retrieves data from multiple tables, so index on lastopen in users won't help much. I would create indexes on columns that are used in JOIN ON clause.

UPDATE

To get desired result, try:

SELECT
u.userid, u.lastopen, 
r.auto_renew_status, r.product_id, r.is_trial_period, 
FROM users as u
JOIN receipt as r ON u.userid = r.id
JOIN (
    SELECT id, count(apd.id) FROM apple_purchase_details
    GROUP BY id
    HAVING apd.id > 5
) as apd ON apd.id = u.userid
WHERE r.is_trial_period = 'false' and r.auto_renew_status = 0