Fetch wikipedia articles query optimization in mysql wikipedia Database

Question

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.

Answer 1

OFFSET must locate, but skip over, the rows. If you can "remember where you left off", performance may be significantly better.

But there is a worse problem with your query. Since there is no ORDER BY, the optimizer is free to provide the rows in any order it likes, thereby leading to missing or duplicated rows.

Even if you add an ORDER BY, OFFSET can give miss or duplicate rows if the table is being added to or deleted from.

Read my blog on this problem, then start over on your query.