MYSQL higher LIMIT offset slow the query

Question

I have the following Mysql query. It is a slow query and will take around 3 seconds to finish. message_id is the primary key. The problem of this is the high LIMIT offset LIMIT 85075, 25.

SELECT * FROM `phorum_messages` WHERE 1  and `catergory` >=0 and parent_id=0   order by `message_id` desc  LIMIT 85075, 25;

Based on this solution, I changed query to the following way. The problem is if I remove where parent_id=0, the speed is much faster. But I really need where parent_id=0

EDIT: I already created index for parent_id and message_id.

Any suggestions? Thanks in advance.

SELECT  t.*
FROM    (
        SELECT  message_id
        FROM    phorum_messages where parent_id=0
        ORDER BY message_id desc LIMIT 85075, 25
        ) q
JOIN    phorum_messages t
ON      t.message_id = q.message_id

score 0 · Answer 1 · answered Mar 27 '13 at 16:01

0

You can create an index on your parent_id column to speed up the query process

CREATE INDEX indexName ON phorum_messages (parent_id);

answered Mar 27 '13 at 16:01

Pouki

1,654
12
18

YES MORE INDEXES, i didnt think they mattered until it cut my outer join calls from 10 minutes to < 1 second. – j_mcnally Mar 27 '13 at 16:02
1

`parent_id` and `message_id` should be in the same index I think, because they are both used in this query. But I am not that good at databases. – aufziehvogel Mar 27 '13 at 16:02
Sorry I forgot to mention I already created index for parent_id – Mar 27 '13 at 16:03
you should also index in category to optimize for this query – j_mcnally Mar 27 '13 at 16:03
@Sop but with which fields, your index can contain multiple fields. YOu should index category and parent_id together and message_id and stated above – j_mcnally Mar 27 '13 at 16:04
@j_mcnally, yes, i already created index for parent_id and message_id. – Mar 27 '13 at 16:06
@Sop oh, if you've already indexes... one time i was surprised by a thing that I found not so logic... But more performant. You could try to replace "ON t.message_id = q.message_id" by "ON t.message_id IN q.message_id"... Let us know if it's more performant to you :) – Pouki Mar 27 '13 at 16:13
@pouki06, if I changed to that way, i got query error report. – Mar 27 '13 at 16:18
@Sop OK, maybe the join cause the error, i tried this trick in a WHERE condition... Sry ! – Pouki Mar 27 '13 at 16:37

score 0 · Answer 2 · answered Mar 27 '13 at 16:02

0

DO you have an index on parent_id or category_id? It doing a full table scan as you have no indexes on the search fields. The queries with limit takes almost same amount of time except for sending data back(which comes after execution phase).

answered Mar 27 '13 at 16:02

georgecj11

1,600
15
22

score 0 · Answer 3 · answered Mar 27 '13 at 16:08

0

When using large LIMIT offsets it is sometimes good to use a different approach that doesn't use a LIMIT offset.

For the first query use

SELECT * FROM `phorum_messages`
WHERE `catergory` >=0 and parent_id=0 
order by `message_id` desc 
LIMIT 25

and save the lowest message_id as $min_id

next_call: Then you can call

SELECT * FROM `phorum_messages`
WHERE `catergory` >=0 and parent_id=0  AND message_id < $min_id
order by `message_id` desc 
LIMIT 25

to get the next 25 results. Set $min_id to the minimum message_id that you got from the result set. goto next_call.

answered Mar 27 '13 at 16:08

Michael

6,451
5
31
53

Thanks Michael, it is a solution for this question. I'm not sure is there any other answer. – Mar 27 '13 at 16:16
I'm using this query for separate pages for forum messages. It is not possible for me to create page NO. 789 without knowing the message_id. Your approach need to create all pages subsequently – Mar 27 '13 at 17:35

score 0 · Answer 4 · answered Mar 27 '13 at 17:06

0

Create a composite index on (parent_id, message_id) and rewrite your query a little:

SELECT  t.*
FROM    (
        SELECT  message_id
        FROM    phorum_messages
        WHERE   parent_id = 0
        ORDER BY
                parent_id DESC, message_id DESC
        LIMIT   85075, 25
        ) q
JOIN    phorum_messages t
ON      t.message_id = q.message_id

answered Mar 27 '13 at 17:06

Quassnoi

413,100
91
616
614

I tried in Phpmyadmin, 'Showing rows 0 - 24 ( 25 total, Query took 1.1760 sec)' It seems still not fast. I created a composite index on parent_id, message_id – Mar 27 '13 at 17:17
@Sop: you can't make it instant, not with large `OFFSET + LIMIT`. Please post the query plan. – Quassnoi Mar 27 '13 at 17:19
I forgot to mention I have total 204,407 rows in phorum_messages table. – Mar 27 '13 at 17:19
`id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 25 1 PRIMARY t eq_ref PRIMARY,message_id PRIMARY 4 q.message_id 1 2 DERIVED phorum_messages ALL parent_id,parent_id_2 parent_id_2 4 204408 Using filesort ` – Mar 27 '13 at 17:23
I don't know how to post query plan. – Mar 27 '13 at 17:24

MYSQL higher LIMIT offset slow the query

4 Answers4