I have imported the Wikipedia database to a local MySQL database and I wrote a query to fetch wiki pages.
My query is as follows :
SELECT
p.page_title,
p.page_id,
t.id,
text.old_text
FROM wikipedia_en.page p
INNER JOIN wikipedia_en.categorylinks cl
ON cl.cl_from = p.page_id AND cl.cl_type = 'page'
AND p.page_namespace = 0
AND p.page_is_redirect = 0
INNER JOIN wikipedia_en.revision ON revision.rev_id = p.page_latest
INNER JOIN wikipedia_en.text ON text.old_id = revision.rev_text_id
-- filter out pages with no topic
INNER JOIN topics.topics t ON cl.cl_to = t.topic
LIMIT 520000, 10000;
The query takes about 2s if the LIMIT
offset starts at 0 but as the offset grows up (e.g 520000) the query execution takes a remarkable time ( e.g for and offset of 520000 it takes about 4min )
Here is the query Explain Plan :
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
| 1 | SIMPLE | t | ALL | topics_topic_index | NULL | NULL | NULL | 141413 | |
| 1 | SIMPLE | cl | ref | PRIMARY,cl_timestamp,cl_sortkey | cl_sortkey | 258 | topics.t.topic,const | 14 | Using where; Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY,name_title,page_redirect_namespace_len | PRIMARY | 4 | wikipedia_en.cl.cl_from | 1 | Using where |
| 1 | SIMPLE | revision | eq_ref | PRIMARY | PRIMARY | 4 | wikipedia_en.p.page_latest | 1 | |
| 1 | SIMPLE | text | eq_ref | PRIMARY | PRIMARY | 4 | wikipedia_en.revision.rev_text_id | 1 | |
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
I'm asking if there is a way to optimize this query using subqueries or temporary tables.
ON
for things that tie the tables together, and move "filters" to aWHERE
clause. WithoutSHOW CREATE TABLE
, we can't give specific advice.