Hi !
For my forum system, I use the following query to generate the
main-overview: (using 4.0.16-log)
select f1.id as BoardId,
f1.name as Board,
f1.more as BoardDesc,
f2.id as AreaId,
f2.name as Area,
f2.more as AreaDesc,
count(distinct f3.id) as ThemenCount,
count(distinct m1.ctime) as MessageCount,
max(m1.ctime) as LastMessageStamp
from forum as f1,
forum as f2
left join forum as f3 on (f3.rid = f2.id)
left join forum_msg as m1 on (m1.fid = f3.id)
where f1.rid = 0
and f2.rid = f1.id
group by AreaId -- note ANSI: group by AreaId, Area, AreaDesc
order by BoardId, AreaId;
ANSI requires to use "group by AreaId, Area, AreaDesc" instead
of "group by AreaId" (which is a documented MySQL shortcut against
this redundancy) but the ANSI notation is ~4 times slower.
This performance penalty is really unnessesary because the optimizer
could detect this kind of redundancy in many cases, especially this
simple case because "group by f2.id" generates clearly the same
results as "group by f2.id, f2.name, f2.more" does.
I think it's really *bad* to allow the non-ANSI shortcut and
to not detect the ANSI-notation as redundant!
shame on you :) :)
--
ciao -
Stefan
" aclocal - emit a warning if -ac kernel is used. "
Stefan Traby Linux/ia32 office: +49-721-3523165
Mathystr. 18-20 V/8 Linux/alpha cell: +XX-XXX-XXXXXXX
76133 Karlsruhe Linux/sparc http://graz03.kwc.at
Germany Linux/arm mailto:[EMAIL PROTECTED]
Europe Linux/mips mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
