Hi!
On Jan 24, Stefan Traby wrote:
> 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.
Yes, you are right.
But though MySQL doesn't have this optimization, I still cannot
understand where this ~4 came from.
How big are your tables (rows and bytes) ? What are typical values for
ThemenCount and MessageCount in the result of this query ?
Can you provide the results of SHOW CREATE TABLE for forum and forum_msg ?
Regards,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
