Trying to optimize a MySQL query that has sub selects

Question

I have the following sql

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount, 
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM client c
LIMIT 50;

This query takes ~45 seconds to run (and I am going to need to run this without a limit) with client having ~80,000 records and ClientLog having ~430,000 records.

Is there anything I can do to the sub selects to increase the performance of this query besides breaking it into separate query calls?

Answer 1

Try runnning the LIMIT 50 against the client table earlier

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount, 
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM (SELECT * FROM client LIMIT 50) c;

or gather the client keys and join a little differently

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = clientkeys.identity AND cll.type = 2) as loginCount, 
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = clientkeys.identity AND clv.type = 10) as viewCount
FROM
(SELECT identity FROM client LIMIT 50) clientkeys INNER JOIN client c USING (identity);

You have to control the subselects by giving them as little data as possible. That's why I suggested running LIMIT 50 before joining to the correlated subqueries.

UPDATE 2012-06-15 12:18 EDT

Since this is a full join, remove the LIMIT 50 from your original query:

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount, 
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM (SELECT * FROM client) c;

and make sure ClientLog has a decent index for the correlated subqueries

ALTER TABLE ClientLog ADD INDEX clientID_type_index (clientID,type);

Give it a Try !!!

Answer 2

In addition to RolandoMySQLDBA answer, you can avoid having 2 subselects :

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
 COUNT(CASE cl.`type` WHEN 2 THEN 1 END) AS loginCount
 COUNT(CASE cl.`type` WHEN 10 THEN 1 END) AS viewCount
 FROM (SELECT * FROM client LIMIT 50)c 
 LEFT JOIN ClientLog cl ON (cl.identity = c.identity)
 GROUP BY c.identity