CODEWITHSUNDEEP
mysqlperformancelimit
Catalin Marcu
Catalin Marcu

Reputation: 644

How to improve ORDER BY ... LIMIT ... query peformance?

I have a table with two columns: id (primary key) and writtenForm (VARCHAR 255). Total number of rows is around 850.000.

I need to perform the following query:

SELECT writtenform FROM `dedict_uniqueWF` order by id LIMIT 1,20000

This query takes more than 10 minutes. When I run explain query, it seems that it's not using the index. Any idea why?

Explain query:

id  select_type     table               type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          dedict_uniqueWF     ALL     NULL            NULL    NULL        NULL    836753  Using filesort

Explain table:

Field           Type            Null    Key     Default     Extra
id              int(11)         NO      PRI     NULL        auto_increment
writtenForm     varchar(255)    YES     NULL

Meanwhile I have added a new index. Still same result in Explain query. Show Indexes:

Table               Non_unique      Key_name        Seq_in_index        Column_name     Collation       Cardinality     Sub_part        Packed      Null    Index_type  Comment     Index_comment
dedict_uniqueWF     0               PRIMARY         1                   id              A               836753          NULL            NULL                BTREE
dedict_uniqueWF     1               writtenForm     1                   writtenForm     A               836753          NULL            NULL        YES     BTREE

Adding a force index use clause will have exactly the same result in explain query (Using filesort):

explain SELECT writtenform FROM `dedict_uniqueWF` use index(PRIMARY) order by id LIMIT 1,20000

Upvotes: 1

Views: 1278

Answers (1)

O. Jones
O. Jones

Reputation: 108651

Try using the deferred-join pattern, as follows, to speed up this query. As it is, you're sorting tons of data.

This query

SELECT id FROM dedict_uniqueWF ORDER BY id LIMIT 1,20000

gets you the ids you need.

Then this query uses that list of ids to access just the required rows.

SELECT writtenform
  FROM dedict_uniqueWF
  JOIN (
           SELECT id FROM dedict_uniqueWF ORDER BY id LIMIT 1,20000
       ) AS a ON dedict_uniqueWF.id = a.id
 ORDER BY a.id

gets you your result set. Look, a 20K row result set is pretty big, and may still take a while to generate and pass from your MySQL server to your application.

You might also try a compound index on (id, writtenform) to accelerate this query. A single-column index on writtenform will serve no useful purpose in this query.

Upvotes: 2

Related Questions