Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you provide the EXPLAIN plan to the question? –  Derek Downey Jun 15 '12 at 15:08
    
What indices do you have on the tables? Can you add the tables definitions? And the query execution plan? –  ypercube Jun 15 '12 at 15:08
add comment

2 Answers 2

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

share|improve this answer
    
This may be useful (maybe) for when the OP wants to use this LIMIT 50. But he mentioned he wants to be running the full select, too. –  ypercube Jun 15 '12 at 15:58
    
In addition, it won't help very much if the ClientLog is big and there are no useful indices. –  ypercube Jun 15 '12 at 15:59
add comment

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 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.