1

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.

1
  • Please use ON for things that tie the tables together, and move "filters" to a WHERE clause. Without SHOW CREATE TABLE, we can't give specific advice.
    – Rick James
    Oct 5, 2016 at 19:40

1 Answer 1

3

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.

1
  • Thank you Rick, I've read your blog post and It was very helpful, I omitted the offset and I used "left off" and the query perfommance is better now.
    – Yacine al
    Oct 6, 2016 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.