Topic on Talk:Quarry

Jump to navigation Jump to search

Subquery succeeds but Join times out

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)[[:>:]]')
PaigePhault (talkcontribs)