User:Catrope/SQL

From mediawiki.org
-- 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                              |
+----+-------------+-----------+-------+---------------+------------+---------+---------------------+------+------------------------------------------+