[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #10 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-12-31 18:21:21 UTC --- (In reply to comment #7) mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id DESC LIMIT 51; ++-+-++---++-++--+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-++---++-++--+---+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++---++-++--+---+ 3 rows in set (0.00 sec) This is a classic pitfall for the MySQL optimizer. What version are you using? On the toolserver, 5.1.53, it avoids the filesort. The problem is that you have a few separate conditions that you need indexes for, aside for join conditions and assuming MySQL is smart enough to do equality propagation (not always a good bet): ct_tag = 'api', ct_repo_id = '3', and the order by/group by. No one index will work for all of them, so MySQL has to choose which index it wants to use. Since there's a LIMIT, the filesort option is probably the worst, but it chooses that option anyway. Often in this case a FORCE INDEX will fix the problem. On the toolserver, it's smart enough to select from code_rev first and not do a filesort, and then do extra scanning on code_tags. In this case, your best bet is probably to add an index on (ct_repo_id, ct_tag, ct_rev_id), if this query is a problem in practice after you stop it from filesorting. If you're not ever ordering or grouping by ct_tag, you can just change the primary key (but I'd guess you sometimes do group by it). I'd also clean up the query a bit to avoid relying on equality propagation, changing cr_repo_id = '3' to cr_repo_id = ct_repo_id and changing GROUP BY cr_id ORDER BY cr_id DESC to GROUP BY ct_rev_id ORDER BY ct_rev_id DESC. But the latter part is probably not necessary, just makes it clearer how you expect the query to execute. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #11 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-12-31 18:22:29 UTC --- (Of course, the query isn't scalable anyway due to COUNT(). It will have to scan arbitrarily many rows. The only way to fully fix it would be to use a summary table of some kind, but those are a pain to maintain, so better not to do that unless you can't fix it some other way. It's not like we're going to have 100 million code reviews anytime soon.) -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #12 from Reedy s...@reedyboy.net 2010-12-31 19:21:15 UTC --- (In reply to comment #10) This is a classic pitfall for the MySQL optimizer. What version are you using? On the toolserver, 5.1.53, it avoids the filesort. re...@ubuntu64-esxi:~$ mysql --version mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1 What comes with Ubuntu 10.10 x64 Server -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #13 from Reedy s...@reedyboy.net 2010-12-31 19:45:12 UTC --- -- Freetext tagging for revisions CREATE TABLE /*_*/code_tags ( ct_repo_id int not null, ct_rev_id int not null, ct_tag varbinary(255) not null, primary key (ct_repo_id,ct_rev_id,ct_tag) ) /*$wgDBTableOptions*/; CREATE INDEX /*i*/ct_repo_id ON /*_*/code_tags (ct_repo_id,ct_tag,ct_rev_id); That suggested index is seemingly already there... It seems like on general you're agreeing with the just leave them be... Versions that improve/fix the optimiser will improve queries, and like you said, we're not going to have millions upon millions of revisions... For the equality propagation, the originals had foo = bar AND bar = 3 (or something similar), i was just poking around and changed for explicit values. Seemingly, the best thing is maybe getting it to do FORCE INDEX.. But it isn't garunteed to help Maybe just remove the technical restrictions people have put in, which are causing some annoyances, and close the bugs...? -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #14 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-12-31 19:49:18 UTC --- Oops, yeah, I missed the index. Try adding FORCE INDEX (ct_repo_id) and see what that gives you. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #15 from Reedy s...@reedyboy.net 2010-12-31 20:01:59 UTC --- mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = ct_repo_id AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id DESC LIMIT 51; ++-+-++-++-++--+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-++-++-++--+---+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author,cr_id | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | Using where | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++-++-++--+---+ 3 rows in set (0.00 sec) mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags` FORCE INDEX (ct_repo_id),`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = ct_repo_id AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id DESC LIMIT 51; ++-+-++-++-++--+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-++-++-++--+---+ | 1 | SIMPLE | mw_code_tags| ref| ct_repo_id | ct_repo_id | 261 | const,const |1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author,cr_id | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | Using where | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++-++-++--+---+ 3 rows in set (0.00 sec) No improvement unfortunately :( -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #16 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-12-31 20:04:49 UTC --- Then it's probably lack of equality propagation. Change cr_id to ct_rev_id in the ORDER BY and GROUP BY clauses. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #17 from Reedy s...@reedyboy.net 2010-12-31 20:07:08 UTC --- mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags` FORCE INDEX (ct_repo_id),`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = ct_repo_id AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY ct_rev_id ORDER BY ct_rev_id DESC LIMIT 51; ++-+-++-++-++--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-+-++-++-++--+--+ | 1 | SIMPLE | mw_code_tags| ref| ct_repo_id | ct_repo_id | 261 | const,const |1 | Using where; Using index | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author,cr_id | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | Using where | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++-++-++--+--+ 3 rows in set (0.00 sec) mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`, `mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = ct_repo_id AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY ct_rev_id ORDER BY ct_rev_id DESC LIMIT 51; ++-+-++-++-++--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-+-++-++-++--+--+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author,cr_id | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | Using where | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++-++-++--+--+ 3 rows in set (0.00 sec) Removing the force index, and just changing the order by/group by improves it... Coool -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #18 from Roan Kattouw roan.katt...@gmail.com 2010-12-31 20:12:02 UTC --- (In reply to comment #11) (Of course, the query isn't scalable anyway due to COUNT(). It will have to scan arbitrarily many rows. The only way to fully fix it would be to use a summary table of some kind, but those are a pain to maintain, so better not to do that unless you can't fix it some other way. It's not like we're going to have 100 million code reviews anytime soon.) Yes, the COUNT() should be killed. Its results aren't displayed in trunk anymore anyway. The point about the code_rev table being smallish is also true, that's why these queries aren't much of a problem in practice. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #19 from Roan Kattouw roan.katt...@gmail.com 2010-12-31 20:17:29 UTC --- (In reply to comment #18) Yes, the COUNT() should be killed. Its results aren't displayed in trunk anymore anyway. Ignore me, this is a different COUNT -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Reedy s...@reedyboy.net changed: What|Removed |Added Blocks||26529 -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Reedy s...@reedyboy.net changed: What|Removed |Added Blocks|26529 | -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Reedy s...@reedyboy.net changed: What|Removed |Added Blocks|23720 | Depends on||23720 -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Reedy s...@reedyboy.net changed: What|Removed |Added Blocks|25437 | Depends on||25437 -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Bug 24479 depends on bug 25437, which changed state. Bug 25437 Summary: Increase release notes range https://bugzilla.wikimedia.org/show_bug.cgi?id=25437 What|Old Value |New Value Status|NEW |RESOLVED Resolution||FIXED -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #20 from Reedy s...@reedyboy.net 2011-01-01 01:22:55 UTC --- So, 2 explicitally reported bugs now fixed. Improved query for Tags in r79365 based on the above help... Wonder what else needs fixing up... -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #7 from Reedy s...@reedyboy.net 2010-12-31 05:53:59 UTC --- It's the order by/group by not being on the first table... mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' LIMIT 51; ++-+-++---++-++--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-+-++---++-++--+--+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++---++-++--+--+ 3 rows in set (0.00 sec) mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id DESC LIMIT 51; ++-+-++---++-++--+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-++---++-++--+---+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++---++-++--+---+ 3 rows in set (0.00 sec) -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #8 from Reedy s...@reedyboy.net 2010-12-31 06:00:11 UTC --- mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' LIMIT 51; ++-+-++---++-++--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-+-++---++-++--+--+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++---++-++--+--+ 3 rows in set (0.00 sec) mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id DESC LIMIT 51; ++-+-++---++-++--+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-++---++-++--+---+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4 | const |1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | mw_code_rev | eq_ref | PRIMARY,cr_repo_id,cr_repo_author | PRIMARY| 8 | const,wikidb.mw_code_tags.ct_rev_id|1 | | | 1 | SIMPLE | mw_code_comment | ref| cc_repo_id,cc_repo_time | cc_repo_id | 8 | wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id |1 | Using index | ++-+-++---++-++--+---+ 3 rows in set (0.00 sec) mysql describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' ORDER BY cr_id DESC LIMIT 51; ++-+-++---++-++--+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-++---++-++--+---+ | 1 | SIMPLE | mw_code_tags| ref| PRIMARY,ct_repo_id | PRIMARY| 4
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #9 from Reedy s...@reedyboy.net 2010-12-31 06:44:21 UTC --- I suppose, also, sorting/group by cr_id isn't much good when it's unindexed directly/as the first column PK is cr_repo_id, cr_id Though, adding the index doesn't help. The discussion that Roan and I had at my house, seemed to suggest that it might be a case of there's nothing we can do about some of these queries. And as such, the hard coded limits in place, might just be removed (fixing the other bugs), and just have to deal with things maybe being a bit slow. If this then annoys domas, maybe he can help improve the queries? ;) -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Reedy s...@reedyboy.net changed: What|Removed |Added Blocks||26394 -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #5 from Reedy s...@reedyboy.net 2010-12-04 19:27:03 UTC --- Special:Code/MediaWiki/tag/api ie SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS comments,cr_path,cr_message,cr_author,cr_timestamp FROM `code_tags`,`code_rev` LEFT JOIN `code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (cr_repo_id=ct_repo_id) AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id DESC LIMIT 51; Filesorts according to VVV -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #6 from Reedy s...@reedyboy.net 2010-12-04 22:05:00 UTC --- vvv Reedy: the generic problem is: there is code_something with cs_repo_id, cs_rev_id and cs_value; the only index is primary key on all three columns in the order listed. There is code_rev which is joined with code_something by cr_repo_id and cr_id. vvv The query on code_something filesorts -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Roan Kattouw roan.katt...@gmail.com changed: What|Removed |Added CC||roan.katt...@gmail.com --- Comment #4 from Roan Kattouw roan.katt...@gmail.com 2010-11-29 22:05:56 UTC --- The query for listing revisions by path does something like WHERE cp_repo_id=1 AND cp_path LIKE '/trunk%' ORDER BY cp_rev_id DESC . If my understanding about how the code_paths table works is correct (will confirm in the morning), we should be able to convert that to AND cp_path='/trunk' , introduce an index on (cp_repo_id, cp_path, cp_rev_id) and that should make this query happy. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Chad H. innocentkil...@gmail.com changed: What|Removed |Added Keywords||bugsmash AssignedTo|wikibug...@lists.wikimedia. |roan.katt...@gmail.com |org | -- 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 Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 --- Comment #3 from Reedy s...@reedyboy.net 2010-10-23 13:47:33 UTC --- I've got the mysql high performance book if anyone wants it for that bug -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Chad H. innocentkil...@gmail.com changed: What|Removed |Added Blocks||25437 -- 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 Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 24479] CodeReview has many unindexed queries
https://bugzilla.wikimedia.org/show_bug.cgi?id=24479 Reedy s...@reedyboy.net changed: What|Removed |Added Summary|CodeReview sucks|CodeReview has many ||unindexed queries --- Comment #2 from Reedy s...@reedyboy.net 2010-07-21 20:08:39 UTC --- Retitling bug. Roan addressed second issue in CR r47395#c7849 -- 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 Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
