Wikia/app — pull request #1317 by ArturD (2013-08-13 17:43:00+00:00)
Title: PLA-460 use left join to filter broken links

Don't merge. Just SQLReview.

@SQLReview

mysql> explain SELECT page_len, page_id, page_title, rd_title, page_namespace, page_is_redirect FROM page IGNORE INDEX (`name_title`) LEFT JOIN redirect ON page_is_redirect = 1 AND page_id = rd_from LEFT JOIN querycache ON qc_title = page_title AND qc_type = 'BrokenRedirects' WHERE page_namespace IN (0) AND (LOWER(page_title) LIKE 'john_pric%') AND ( qc_type IS NULL ) LIMIT 6;
+----+-------------+------------+--------+---------------+---------+---------+---------------------+--------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+---------------------+--------+-------------------------+
| 1 | SIMPLE | page | ALL | NULL | NULL | NULL | NULL | 149253 | Using where |
| 1 | SIMPLE | redirect | eq_ref | PRIMARY | PRIMARY | 4 | muppet.page.page_id | 1 | |
| 1 | SIMPLE | querycache | ref | qc_type | qc_type | 32 | const | 4 | Using where; Not exists |
+----+-------------+------------+--------+---------------+---------+---------+---------------------+--------+-------------------------+
3 rows in set (0.00 sec)