From: DaB. <dab@ts.wikimedia.org>

Date: Sun, 06 Jan 2008 01:52:10

SELECT page_namespace,page_title,page_len
FROM revision,page
LEFT JOIN templatelinks
ON tl_from = page_id
WHERE page_len<50
AND page_is_redirect = 0
AND page_namespace NOT IN (2, 3)
AND rev_page=page_id
AND tl_from IS NULL
GROUP BY rev_user
HAVING count(DISTINCT rev_user)<2
ORDER BY page_len
LIMIT 20;

seems to do what you want (the join with toolserver.namespace is missing, that's trivial ![][1]). But perhaps Duesentrieb knows a better way ![][2]

Sincerly,
DaB.

P.S: on dewp the query needs 8 min 5.23 sec and 0.31 sec on cswp.

[1]: https://jira.toolserver.org/images/icons/emoticons/wink.gif
[2]: https://jira.toolserver.org/images/icons/emoticons/smile.gif

From: Daniel Kinzler <daniel@brightbyte.de>

Date: Sun, 06 Jan 2008 14:16:01

The result is at http://tools.wikimedia.de/~daniel/misc/shortpages-enwiki.txt (39441), details at http://wiki.ts.wikimedia.org/view/Query_service/short_pages_enwiki.

I used the original query, only cleaned it up a bit. I got rid of the first subquery, but as it turns out, mysql was already smart enough to optimize it away anyway.

@DaB: avoiding the second subquery at the cost of running a GROUP on all revisions on enwiki probably makes things worse, not better. Running group on such a huge table is expensive, and running a simple subquery on a set of relatively few pages is probably faster - this is true because page_len < 50 is a pretty strong limitation; if we had no such strong resatriction, grouping would probably be faster.

From: DaB. <dab@ts.wikimedia.org>

Date: Sun, 06 Jan 2008 23:28:51

mm, your query is a little bit slower then mine. Your query needs 10 min 26.73 sec on dewp and 1 min 24.52 sec on cswp. Any idea, why?

(That's only a question to learn ![][1])

[1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif

From: Daniel Kinzler <daniel@brightbyte.de>

Date: Mon, 07 Jan 2008 00:14:00

when i tested, it was a bit faster ![][1] the problem is: caches. to get a reliable benchmark, you would have to try each on a cold cache. if you run either qiery twice, the second go is much faster. this may also make the other query faster. also, some query run by someone else just before you tried may have loaded the relevant index into the cache, etc...

[1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif

From: Andreas Gasser <andreas_gasser@yahoo.com>

Date: Tue, 28 Jun 2011 10:09:48

http://toolserver.org/

This bug was imported as RESOLVED. The original assignee has therefore not been
set, and the original reporters/responders have not been added as CC, to
prevent bugspam.

If you re-open this bug, please consider adding these people to the CC list:
Original assignee: (none)
CC list: b@mzmcbride.com, wikimedia-bugzilla@dabpunkt.eu, daniel.kinzler@wikimedia.de