-- BrokenRedirects
EXPLAIN SELECT p1.page_namespace AS namespace, p1.page_title AS title,
rd_namespace, rd_title
FROM redirect
LEFT JOIN page AS p1 ON rd_from=p1.page_id
LEFT JOIN page AS p2 ON rd_namespace=p2.page_namespace AND rd_title=p2.page_title
WHERE rd_namespace >= 0 -- Probably used to force a range scan, obsolete now?
AND p2.page_namespace IS NULL
ORDER BY rd_namespace, rd_title, rd_from
LIMIT 50
+----+-------------+----------+--------+---------------+-------------+---------+---------------------------------------------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+-------------+---------+---------------------------------------------------+------+--------------------------------------+
| 1 | SIMPLE | redirect | index | rd_ns_title | rd_ns_title | 265 | NULL | 2 | Using where; Using index |
| 1 | SIMPLE | p1 | eq_ref | PRIMARY | PRIMARY | 4 | wiki.redirect.rd_from | 1 | |
| 1 | SIMPLE | p2 | eq_ref | name_title | name_title | 261 | wiki.redirect.rd_namespace,wiki.redirect.rd_title | 1 | Using where; Using index; Not exists |
+----+-------------+----------+--------+---------------+-------------+---------+---------------------------------------------------+------+--------------------------------------+
-- Deadendpages
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN pagelinks ON page_id=pl_from
WHERE pl_from IS NULL
AND page_namespace='0'
AND page_is_redirect='0'
ORDER BY page_title
LIMIT 50;
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+--------------------------------------+
| 1 | SIMPLE | page | ref | name_title | name_title | 4 | const | 13 | Using where |
| 1 | SIMPLE | pagelinks | ref | pl_from | pl_from | 4 | wiki.page.page_id | 11 | Using where; Using index; Not exists |
+----+-------------+-----------+------+---------------+------------+---------+-------------------+------+--------------------------------------+
-- DoubleRedirects
EXPLAIN SELECT pa.page_namespace AS namespace, pa.page_title AS title, pb.page_namespace AS nsb,
pb.page_title AS pb, pc.page_namespace AS nsc, pc.page_title AS pc
FROM redirect AS ra, redirect AS rb, page AS pa, page AS pb, page AS pc
WHERE ra.rd_from = pa.page_id
AND pb.page_namespace = ra.rd_namespace
AND pb.page_title = ra.rd_title
AND rb.rd_from = pb.page_id
AND pc.page_namespace = rb.rd_namespace
AND pc.page_title = rb.rd_title
ORDER BY ra.rd_namespace, ra.rd_title
LIMIT 50;
+----+-------------+-------+--------+---------------------+-------------+---------+---------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+-------------+---------+---------------------------------------+------+-------------+
| 1 | SIMPLE | ra | index | PRIMARY,rd_ns_title | rd_ns_title | 265 | NULL | 2 | Using index |
| 1 | SIMPLE | pa | eq_ref | PRIMARY | PRIMARY | 4 | wiki.ra.rd_from | 1 | |
| 1 | SIMPLE | pb | eq_ref | PRIMARY,name_title | name_title | 261 | wiki.ra.rd_namespace,wiki.ra.rd_title | 1 | Using index |
| 1 | SIMPLE | rb | eq_ref | PRIMARY,rd_ns_title | PRIMARY | 4 | wiki.pb.page_id | 1 | |
| 1 | SIMPLE | pc | eq_ref | name_title | name_title | 261 | wiki.rb.rd_namespace,wiki.rb.rd_title | 1 | Using index |
+----+-------------+-------+--------+---------------------+-------------+---------+---------------------------------------+------+-------------+
-- DoubleRedirects with a specific title:
EXPLAIN SELECT pa.page_namespace AS namespace, pa.page_title AS title, pb.page_namespace AS nsb,
pb.page_title AS pb, pc.page_namespace AS nsc, pc.page_title AS pc
FROM redirect AS ra, redirect AS rb, page AS pa, page AS pb, page AS pc
WHERE ra.rd_from = pa.page_id
AND pb.page_namespace = ra.rd_namespace
AND pb.page_title = ra.rd_title
AND rb.rd_from = pb.page_id
AND pc.page_namespace = rb.rd_namespace
AND pc.page_title = rb.rd_title
AND pa.page_namespace = '0'
AND pa.page_title='Foo'
ORDER BY ra.rd_namespace, ra.rd_title
LIMIT 50;
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
| 1 | SIMPLE | pa | const | PRIMARY,name_title | name_title | 261 | const,const | 1 | Using index |
| 1 | SIMPLE | ra | const | PRIMARY,rd_ns_title | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | pb | const | PRIMARY,name_title | name_title | 261 | const,const | 1 | Using index |
| 1 | SIMPLE | rb | const | PRIMARY,rd_ns_title | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | pc | const | name_title | name_title | 261 | const,const | 1 | Using index |
+----+-------------+-------+-------+---------------------+------------+---------+-------------+------+-------------+
-- Listredirects
EXPLAIN SELECT p1.page_namespace AS namespace, p1.page_title AS title, rd_namespace, rd_title, p2.page_id AS redirid
FROM page AS p1
LEFT JOIN redirect ON rd_from=p1.page_id
LEFT JOIN page AS p2 ON p2.page_namespace=rd_namespace AND p2.page_title=rd_title
WHERE p1.page_is_redirect='1'
ORDER BY p1.page_namespace, p1.page_title
LIMIT 50;
+----+-------------+----------+--------+---------------+------------+---------+---------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+------------+---------+---------------------------------------------------+------+-------------+
| 1 | SIMPLE | p1 | index | NULL | name_title | 261 | NULL | 122 | Using where |
| 1 | SIMPLE | redirect | eq_ref | PRIMARY | PRIMARY | 4 | wiki.p1.page_id | 1 | |
| 1 | SIMPLE | p2 | eq_ref | name_title | name_title | 261 | wiki.redirect.rd_namespace,wiki.redirect.rd_title | 1 | Using index |
+----+-------------+----------+--------+---------------+------------+---------+---------------------------------------------------+------+-------------+
-- Lonelypages
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN pagelinks ON pl_namespace=page_namespace AND pl_title=page_title
LEFT JOIN templatelinks ON tl_namespace=page_namespace AND tl_title=page_title
WHERE pl_namespace IS NULL
AND tl_namespace IS NULL
AND page_namespace='0'
AND page_is_redirect='0'
ORDER BY page_title
LIMIT 50;
+----+-------------+---------------+------+---------------+--------------+---------+-----------------------------------------------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+--------------+---------+-----------------------------------------------+------+--------------------------------------+
| 1 | SIMPLE | page | ref | name_title | name_title | 4 | const | 15 | Using where |
| 1 | SIMPLE | pagelinks | ref | pl_namespace | pl_namespace | 261 | wiki.page.page_namespace,wiki.page.page_title | 1 | Using where; Using index; Not exists |
| 1 | SIMPLE | templatelinks | ref | tl_namespace | tl_namespace | 261 | wiki.page.page_namespace,wiki.page.page_title | 1 | Using where; Using index; Not exists |
+----+-------------+---------------+------+---------------+--------------+---------+-----------------------------------------------+------+--------------------------------------+
-- Shortpages
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_len AS value
FROM page FORCE INDEX(page_len)
WHERE page_namespace = '0'
AND page_is_redirect = '0'
ORDER BY page_title
LIMIT 50;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | page | index | NULL | page_len | 4 | NULL | 122 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
-- EXPLAINs run on local setup above this line
-- EXPLAINs run on toolserver below this line
-- Uncategorized*
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN categorylinks ON cl_from=page_id
WHERE cl_from IS NULL
AND page_namespace='0' -- Or '6', '10', '14'
AND page_is_redirect='0'
ORDER BY value
LIMIT 50;
+----+-------------+---------------+------+---------------+------------+---------+---------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------------+---------+---------------------+---------+--------------------------+
| 1 | SIMPLE | page | ref | name_title | name_title | 4 | const | 8254888 | Using where |
| 1 | SIMPLE | categorylinks | ref | cl_from | cl_from | 4 | enwiki.page.page_id | 1 | Using where; Using index |
+----+-------------+---------------+------+---------------+------------+---------+---------------------+---------+--------------------------+
-- Unusedcategories
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN categorylinks ON cl_to=page_title
WHERE cl_from IS NULL
AND page_namespace='14'
AND page_is_redirect='0'
ORDER BY value
LIMIT 50;
+----+-------------+---------------+------+-------------------------+------------+---------+------------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+-------------------------+------------+---------+------------------------+---------+--------------------------+
| 1 | SIMPLE | page | ref | name_title | name_title | 4 | const | 1701966 | Using where |
| 1 | SIMPLE | categorylinks | ref | cl_sortkey,cl_timestamp | cl_sortkey | 257 | enwiki.page.page_title | 22 | Using where; Using index |
+----+-------------+---------------+------+-------------------------+------------+---------+------------------------+---------+--------------------------+
-- Unusedimages
EXPLAIN SELECT '6' AS namespace, img_name AS title, img_timestamp AS value, img_user, img_user_text, img_description
FROM image
LEFT JOIN imagelinks ON il_to=img_name
WHERE il_to IS NULL
ORDER BY value
LIMIT 50;
+----+-------------+------------+-------+---------------+---------------+---------+-----------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------------+---------+-----------------------+------+--------------------------+
| 1 | SIMPLE | image | index | NULL | img_timestamp | 16 | NULL | 7 | |
| 1 | SIMPLE | imagelinks | ref | il_to | il_to | 257 | enwiki.image.img_name | 7 | Using where; Using index |
+----+-------------+------------+-------+---------------+---------------+---------+-----------------------+------+--------------------------+
-- Unusedimages with $wgCountCategorizedImagesAsUsed == true
EXPLAIN SELECT '6' AS namespace, img_name AS title, img_timestamp AS value, img_user, img_user_text, img_description
FROM page
LEFT JOIN categorylinks ON cl_from=page_id
LEFT JOIN imagelinks ON il_to=page_title
INNER JOIN image ON img_name=page_title
WHERE il_to IS NULL
AND cl_from IS NULL
AND page_namespace='6'
ORDER BY value
LIMIT 50;
+----+-------------+---------------+--------+---------------+---------------+---------+-----------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+--------+---------------+---------------+---------+-----------------------------+------+--------------------------+
| 1 | SIMPLE | image | index | PRIMARY | img_timestamp | 16 | NULL | 7 | |
| 1 | SIMPLE | page | eq_ref | name_title | name_title | 261 | const,enwiki.image.img_name | 1 | Using where; Using index |
| 1 | SIMPLE | categorylinks | ref | cl_from | cl_from | 4 | enwiki.page.page_id | 1 | Using where; Using index |
| 1 | SIMPLE | imagelinks | ref | il_to | il_to | 257 | enwiki.image.img_name | 7 | Using where; Using index |
+----+-------------+---------------+--------+---------------+---------------+---------+-----------------------------+------+--------------------------+
-- Unusedtemplates
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN templatelinks ON tl_title=page_title AND tl_namespace=page_namespace
WHERE page_namespace='10'
AND tl_from IS NULL
AND page_is_redirect='0'
ORDER BY value
LIMIT 50;
+----+-------------+---------------+------+---------------+--------------+---------+---------------------------------------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+--------------+---------+---------------------------------------------------+---------+--------------------------+
| 1 | SIMPLE | page | ref | name_title | name_title | 4 | const | 771834 | Using where |
| 1 | SIMPLE | templatelinks | ref | tl_namespace | tl_namespace | 261 | enwiki.page.page_namespace,enwiki.page.page_title | 7518652 | Using where; Using index |
+----+-------------+---------------+------+---------------+--------------+---------+---------------------------------------------------+---------+--------------------------+
-- Unwatchedpages
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_namespace AS value
FROM page
LEFT JOIN watchlist ON wl_title=page_title AND wl_namespace=page_namespace
WHERE wl_title IS NULL
AND page_is_redirect='0'
AND page_namespace != '8'
ORDER BY page_namespace, page_title
LIMIT 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE page range name_title name_title 4 NULL 71 Using where
1 SIMPLE watchlist ref namespace_title namespace_title 261 wiki.page.page_namespace,wiki.page.page_title 1 Using where; Using index; Not exists
-- Withoutinterwiki
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN langlinks ON ll_from=page_id
WHERE ll_title IS NULL
ORDER BY page_namespace, page_title
LIMIT 50;
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+-------------+
| 1 | SIMPLE | page | index | NULL | name_title | 261 | NULL | 16 | Using index |
| 1 | SIMPLE | langlinks | ref | ll_from | ll_from | 4 | enwiki.page.page_id | 3 | Using where |
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+-------------+
-- Withoutinterwiki with prefix
-- TODO: Fix this
EXPLAIN SELECT page_namespace AS namespace, page_title AS title, page_title AS value
FROM page
LEFT JOIN langlinks ON ll_from=page_id
WHERE ll_title IS NULL
AND page_namespace='0'
AND page_title LIKE 'Foo%'
ORDER BY page_namespace, page_title
LIMIT 50;
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+------------------------------------------+
| 1 | SIMPLE | page | range | name_title | name_title | 261 | NULL | 8140 | Using where; Using index; Using filesort |
| 1 | SIMPLE | langlinks | ref | ll_from | ll_from | 4 | enwiki.page.page_id | 3 | Using where |
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+------------------------------------------+
