MySQL nested SELECT too slow

Question

I have a script of the following structure:

SELECT SUM(CASE WHEN pf.info IS NOT NULL THEN 1 ELSE 0 END)
FROM summary s 
LEFT JOIN (SELECT id, info FROM items GROUP BY id) pf ON s.id=pf.id
GROUP BY s.date

What I want is to count those id's which are in 'summary' and present in 'items'. 'items' have same id's repeated several times, that's why I do GROUP BY.

This script works as I want, but it is extremely slow, much slower than just doing straightforward LEFT JOIN (and counting each id several times). This doesn't seem to make sense since I need a smaller subspace of that and it should be easier.

So the question is: how to restructure the query to make it quicker?

`SELECT id, info FROM items GROUP BY id` --- what `info` is selected with this query? What if some particular `id` has several infos? — zerkms, Sep 21 '12 at 02:07

score 3 · Accepted Answer · answered Sep 21 '12 at 02:08

3

Use count(distinct ...):

SELECT count(distinct s.id)
FROM summary s 
JOIN items i ON s.id = i.id

I don't understand why you are grouping by s.date - there's no clue in your question as to why, so if it's not a mistake and you need to group by date, use this:

SELECT s.date, count(distinct s.id)
FROM summary s 
JOIN items i ON s.id = i.id
GROUP BY s.date

answered Sep 21 '12 at 02:08

Bohemian

412,405
93
575
722

This query doesn't do entirely the same. In original query there could be several rows with the same `s.id` in the result set – zerkms Sep 21 '12 at 02:09
@zerkms then it's not an "id". I'm willing to bet it is a key. – Bohemian Sep 21 '12 at 02:10
Thank you, that works. That was easy, sorry for stupid question :) – sashkello Sep 21 '12 at 05:01

MySQL nested SELECT too slow

1 Answers1