Bug #4885 combining subqueries with group by
Submitted: 4 Aug 2004 2:16 Modified: 2 Feb 2009 11:29
Reporter: Mark Nozz Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1.3 OS:Microsoft Windows (windows)
Assigned to: CPU Architecture:Any

[4 Aug 2004 2:16] Mark Nozz
Description:
query (~ms):SELECT CM.nid FROM comments CM GROUP BY CM.nid ORDER BY CM.date DESC LIMIT 15 
work fine with some ms
i added a simple subquery which take couple of ms with other non group by queries .. but resulting query with subquery and group by takes 1+ seconds (instead of query time + 15 * subquery time .. probably less)
subquery (~ms): 
(SELECT COUNT(C.cid) FROM comments C where C.nid=CM.nid AND C.ctyp=CM.ctyp) as num
resulting query (1+ ms):
SELECT (SELECT COUNT(C.cid) FROM comments C where C.nid=CM.nid AND C.ctyp=CM.ctyp) as num,CM.nid FROM comments CM GROUP BY CM.nid ORDER BY CM.date DESC LIMIT 15

nid -> unsigned smallint
date -> datetime
only using nid in table with 1000+ records, and as records growing up the process time grows up much more
so i tried to set index to nid and time degrades rapidly to ~2ms, but as soon as i add another subqueries (which arent used in order by and group by) its again growed up to ~150ms and i think its too much
so as long as im adding subqueries i must add indexes :/
finally .. until the subquery will take ~ms w/o indexes i wont understand why the whole query take 1+ sec

How to repeat:
some above, some lack, some lazy :P

Suggested fix:
The algorythm should subquery only output lines if columns which subquery generates arent needed in order or group by
I dont know the algorythm even source..i only seeing this as roof for my (sub)queries :P
[2 Jan 2009 11:29] Valeriy Kravchuk
Please, check how this kind of subqueries are handled by MySQL 6.0.8.
[3 Feb 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".