Topic on Talk:Quarry

Jump to navigation Jump to search

Subquery succeeds but Join times out

3
PaigePhault (talkcontribs)

I'm trying to get some estimates of the prevalence of WP policy violations, but am seeing timeouts for queries that do joins.

SELECT page.page_title, revision.rev_id, mycomment.comment_text FROM (SELECT comment.comment_text, comment.comment_id FROM comment WHERE comment.comment_text RLIKE '[[:<:]]WP:(OUTING|PRIVACY|DOX)[[:>:]]' LIMIT 10) AS mycomment INNER JOIN revision ON revision.rev_comment_id = mycomment.comment_id INNER JOIN page on page.page_id = revision.rev_page;

The internal select statement finishes in a <cough> reasonable amount of time, but trying to use it in a join fails every time. Any suggestions on how to materialize the subselect perhaps?

Matěj Suchánek (talkcontribs)

What about...?

SELECT page_title, rev_id, comment_text
FROM page
JOIN revision ON rev_page = page_id
JOIN `comment` ON rev_comment_id = comment_id
WHERE rev_comment_id IN (SELECT comment_id FROM `comment` AS c1 WHERE c1.comment_text RLIKE '[[:<:]]WP:(OUTING|PRIVACY|DOX)[[:>:]]')
LIMIT 10;
PaigePhault (talkcontribs)
Reply to "Subquery succeeds but Join times out"