[Bug 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #15 from Roan Kattouw roan.katt...@gmail.com 2012-03-29 10:34:43 UTC --- (In reply to comment #10) (In reply to comment #3) Hi, thanks for your interest! The output of the explain select is exactly the same if I run with or without mixed, are you sure it is the correct test for checking performance ? idselect_typetabletypepossible_keyskeykey_len ref rowsExtra 1SIMPLEpageALLPRIMARYNULLNULLNULL1Using temporary; Using filesort 1SIMPLErevisionrangeusertext_timestamp usertext_timestamp 257NULL2Using where; Using join buffer (and 257 becomes 271 if there is a uccontinue condition) Yes, EXPLAIN is normally used to get an indication of the database performance. From the indices on the revision table it looks like this should work, but I think somebody who knows more about query performance should have a look at this. Both ALL and Using filesort are red flags. Here are my explains from the toolserver: mysql explain select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_user_text, rev_timestamp limit 51 \G *** 1. row *** id: 1 select_type: SIMPLE table: revision type: range possible_keys: PRIMARY,page_timestamp,usertext_timestamp key: usertext_timestamp key_len: 257 ref: NULL rows: 46784760 Extra: Using where; Using index *** 2. row *** id: 1 select_type: SIMPLE table: page type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: enwiki.revision.rev_page rows: 1 Extra: 2 rows in set (0.00 sec) mysql explain select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_timestamp, rev_user_text limit 51 \G *** 1. row *** id: 1 select_type: SIMPLE table: revision type: range possible_keys: PRIMARY,page_timestamp,usertext_timestamp key: usertext_timestamp key_len: 257 ref: NULL rows: 46784760 Extra: Using where; Using index; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: page type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: enwiki.revision.rev_page rows: 1 Extra: 2 rows in set (0.00 sec) Here's what happened when I ran these queries on the toolserver: mysql select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_user_text, rev_timestamp limit 51; [...] 51 rows in set (0.00 sec) mysql select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_timestamp, rev_user_text limit 51; [after about a minute] ^CCtrl-C -- sending KILL QUERY 9922979 to server ... Ctrl-C -- query aborted. ERROR 1028 (HY000): Sort aborted This makes sense when you consider how MySQL is executing this query: it fetches all rows whose user starts with a J (according to EXPLAIN there are approximately 46 million of these), then sorts those by (timestamp,user) using a slow filesort (unindexed sort). So yes, this patch has major performance implications. An API query like ucuserprefix=Jucsort=timestamp would result in an SQL query that takes over a minute to execute. -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #16 from Immae ismael.bo...@normalesup.org 2012-03-29 10:43:48 UTC --- Hi, Thanks for your explanations. Would it be the same if we use ucuser instead of ucuserprefix? (i.e. we forbid to use it on ucuserprefix?) Maybe the discussion should continue on gerrit since I reposted it there? -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #17 from Immae ismael.bo...@normalesup.org 2012-03-29 10:49:45 UTC --- If I understood well what you said, the problem is when mysql tries to sort the result, which is much more smaller when there are only a limited number of users? How long would a query take in that last case? -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #18 from Roan Kattouw roan.katt...@gmail.com 2012-03-29 11:02:14 UTC --- (In reply to comment #16) Hi, Thanks for your explanations. Would it be the same if we use ucuser instead of ucuserprefix? (i.e. we forbid to use it on ucuserprefix?) Maybe the discussion should continue on gerrit since I reposted it there? With a single user it would work, but then sorting by (user,timestamp) produces the same output as sorting by (timestamp,user) because the value of 'user' is always the same. As soon as you allow multiple users, you run into the slow query problem: the 500 most active users on enwiki probably account for a couple million edits. -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 Immae ismael.bo...@normalesup.org changed: What|Removed |Added Status|UNCONFIRMED |RESOLVED Resolution||INVALID --- Comment #19 from Immae ismael.bo...@normalesup.org 2012-03-29 11:05:41 UTC --- Ok thanks. I'll try to close the bugreport if I manage to... -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 Bryan Tong Minh bryan.tongm...@gmail.com changed: What|Removed |Added Resolution|INVALID |WONTFIX -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #13 from Immae ismael.bo...@normalesup.org 2012-03-27 20:54:58 UTC --- https://gerrit.wikimedia.org/r/3825 -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #14 from Immae ismael.bo...@normalesup.org 2012-03-27 20:56:35 UTC --- (was that the correct adress I was supposed to give? It wasn't very clear in the documentation, certainly because of my not-so-good english) -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #11 from Immae ismael.bo...@normalesup.org 2012-03-24 23:50:19 UTC --- Should I resubmit it to gerrit now that it is open or will it do there? -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #12 from Sumana Harihareswara suma...@panix.com 2012-03-25 02:15:15 UTC --- Immae, go ahead and submit your change to Git via Gerrit. Thanks! -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #10 from Bryan Tong Minh bryan.tongm...@gmail.com 2012-03-21 20:57:43 UTC --- (In reply to comment #3) Hi, thanks for your interest! The output of the explain select is exactly the same if I run with or without mixed, are you sure it is the correct test for checking performance ? idselect_typetabletypepossible_keyskeykey_lenref rowsExtra 1SIMPLEpageALLPRIMARYNULLNULLNULL1Using temporary; Using filesort 1SIMPLErevisionrangeusertext_timestampusertext_timestamp 257NULL2Using where; Using join buffer (and 257 becomes 271 if there is a uccontinue condition) Yes, EXPLAIN is normally used to get an indication of the database performance. From the indices on the revision table it looks like this should work, but I think somebody who knows more about query performance should have a look at this. -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 Sumana Harihareswara suma...@panix.com changed: What|Removed |Added CC||suma...@panix.com Summary|Patch for usercontribs |usercontribs API: sort ||results by timestamp,user ||or user,timestamp --- Comment #2 from Sumana Harihareswara suma...@panix.com 2012-03-20 12:49:12 UTC --- Thanks for the patch! Also, you might be interested in getting a Git account so you can submit future patches more easily: https://labsconsole.wikimedia.org/wiki/Help:Access -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #3 from Immae ismael.bo...@normalesup.org 2012-03-20 15:26:51 UTC --- Hi, thanks for your interest! The output of the explain select is exactly the same if I run with or without mixed, are you sure it is the correct test for checking performance ? idselect_typetabletypepossible_keyskeykey_lenref rowsExtra 1SIMPLEpageALLPRIMARYNULLNULLNULL1Using temporary; Using filesort 1SIMPLErevisionrangeusertext_timestampusertext_timestamp 257NULL2Using where; Using join buffer (and 257 becomes 271 if there is a uccontinue condition) By the way, I deleted an important part of the patch when I cleared all the changes I made in the file, I just resubmitted - and triple checked that I didn't delete anything else - another one. @Sumana: The developper page said that Git will only work from tomorrow, otherwise I would have tried to use it instead of the instructions with SVN :) . I'll think about it, but maybe I will only be an occasionnal contributor to wikimedia -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 Immae ismael.bo...@normalesup.org changed: What|Removed |Added Attachment #10286|0 |1 is obsolete|| --- Comment #4 from Immae ismael.bo...@normalesup.org 2012-03-20 15:27:51 UTC --- Created attachment 10289 -- https://bugzilla.wikimedia.org/attachment.cgi?id=10289 second patch, corrected -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 duplicate...@googlemail.com changed: What|Removed |Added CC||duplicate...@googlemail.com --- Comment #5 from duplicate...@googlemail.com 2012-03-20 20:26:38 UTC --- What about a parameter ucsort with the values user (as default) and timestamp like list=categorymembers with sortkey and timestamp has, instead of parameter mixed? -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #6 from Immae ismael.bo...@normalesup.org 2012-03-20 22:37:54 UTC --- If there is something already existant like that it would be a good thing to do that yes, do you want me to adapt the patch or will the maintainer do it? (it's only a matter of minutes to do that...) -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 Mark A. Hershberger m...@everybody.org changed: What|Removed |Added CC||m...@everybody.org --- Comment #7 from Mark A. Hershberger m...@everybody.org 2012-03-20 23:15:53 UTC --- (In reply to comment #6) If there is something already existant like that it would be a good thing to do that yes, do you want me to adapt the patch or will the maintainer do it? (it's only a matter of minutes to do that...) It would be good if you would since it makes your patch immediately more usable to us. -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 Immae ismael.bo...@normalesup.org changed: What|Removed |Added Attachment #10289|0 |1 is obsolete|| --- Comment #8 from Immae ismael.bo...@normalesup.org 2012-03-20 23:35:48 UTC --- Created attachment 10292 -- https://bugzilla.wikimedia.org/attachment.cgi?id=10292 New ucsort key instead of mixed -- 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 35349] usercontribs API: sort results by timestamp,user or user,timestamp
https://bugzilla.wikimedia.org/show_bug.cgi?id=35349 --- Comment #9 from Immae ismael.bo...@normalesup.org 2012-03-20 23:37:50 UTC --- No problem, just resubmitted it :) Since I didn't have any clue that any of you where maintainers or simple users I didn't know whether I reached the correct audience or not, and whether you where interested in the patch or not. -- 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
