https://bugzilla.wikimedia.org/show_bug.cgi?id=21196
--- Comment #2 from Grega Bremec <[email protected]> 2009-10-20 19:36:46 UTC --- Oops, sorry about the testme tag... I guess I didn't read carefully enough. :-/ Anyways, having some modest interest in this being fixed, I'm going to persevere, ;-) The other two options would be to: - use named columns from mwuser and group by each and every one of them or - switch the join tables and make the other table a subselect - afterall, the query wants user data and just uses revisions as a means to weed records out Both (especially the other one, which is better in terms of performance, but more questionable in terms of compatibility, see query plans below) work in MySQL 4.1 and newer as well (http://dev.mysql.com/doc/refman/4.1/en/unnamed-views.html). Forgot to mention yesterday, both changes also fix for 18078 - it's very related. Hope this helped in any way. Kind regards, Grega -- The two queries: a) group by SELECT m.user_id, m.user_name, m.user_real_name, m.user_email, m.user_editcount, MAX(r.rev_timestamp) AS timestamp FROM $userTable AS m LEFT JOIN $revTable AS r ON r.rev_user = m.user_id WHERE r.rev_page = $pageId AND r.rev_user != $user AND r.rev_deleted & $hideBit = 0 GROUP BY m.user_id, m.user_name, m.user_real_name, m.user_email, m.user_editcount ORDER BY timestamp DESC b) subselect SELECT m.*, r.timestamp FROM $userTable AS m JOIN (SELECT rev_user, MAX(rev_timestamp) AS timestamp FROM $revTable WHERE rev_page = $pageId AND rev_user != $user AND rev_deleted & $hideBit = 0 GROUP BY rev_user ORDER BY timestamp DESC) AS r ON m.user_id = r.rev_user; What the PostgreSQL planner thinks of the two: wiki=# explain analyze select m.user_id, m.user_name, m.user_real_name, m.user_email, m.user_editcount, max(r.rev_timestamp) as timestamp from mwuser m left join revision r on r.rev_user = m.user_id where r.rev_page = 3 and r.rev_user != 1 and r.rev_deleted & 4 = 0 group by m.user_id, m.user_name, m.user_real_name, m.user_email, m.user_editcount order by timestamp desc; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Sort (cost=7.50..7.51 rows=1 width=112) (actual time=0.361..0.363 rows=1 loops=1) Sort Key: (max(r.rev_timestamp)) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=7.48..7.49 rows=1 width=112) (actual time=0.344..0.346 rows=1 loops=1) -> Nested Loop (cost=0.00..7.47 rows=1 width=112) (actual time=0.068..0.296 rows=6 loops=1) Join Filter: (m.user_id = r.rev_user) -> Seq Scan on revision r (cost=0.00..6.33 rows=1 width=12) (actual time=0.031..0.114 rows=6 loops=1) Filter: ((rev_user <> 1) AND (rev_page = 3) AND (((rev_deleted)::integer & 4) = 0)) -> Seq Scan on mwuser m (cost=0.00..1.06 rows=6 width=104) (actual time=0.003..0.012 rows=6 loops=6) Total runtime: 0.521 ms (10 rows) wiki=# explain analyze select m.user_id, m.user_name, m.user_real_name, m.user_email, m.user_editcount, r.timestamp from mwuser m join (select rev_user, max(rev_timestamp) as timestamp from revision where rev_page = 3 and rev_user != 1 and rev_deleted & 4 = 0 group by rev_user order by timestamp desc) r on m.user_id = rev_user; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=6.39..7.48 rows=1 width=112) (actual time=0.244..0.250 rows=1 loops=1) Hash Cond: (m.user_id = r.rev_user) -> Seq Scan on mwuser m (cost=0.00..1.06 rows=6 width=104) (actual time=0.013..0.024 rows=6 loops=1) -> Hash (cost=6.37..6.37 rows=1 width=12) (actual time=0.191..0.191 rows=1 loops=1) -> Subquery Scan r (cost=6.36..6.37 rows=1 width=12) (actual time=0.181..0.186 rows=1 loops=1) -> Sort (cost=6.36..6.36 rows=1 width=12) (actual time=0.176..0.178 rows=1 loops=1) Sort Key: (max(revision.rev_timestamp)) Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=6.34..6.35 rows=1 width=12) (actual time=0.155..0.160 rows=1 loops=1) -> Seq Scan on revision (cost=0.00..6.33 rows=1 width=12) (actual time=0.027..0.122 rows=6 loops=1) Filter: ((rev_user <> 1) AND (rev_page = 3) AND (((rev_deleted)::integer & 4) = 0)) Total runtime: 0.385 ms (12 rows) -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. You are on the CC list for the bug. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
