Mysql limit offset slow down with large offset value

Question

My query takes lots of time 80sec, 70sec etc. I am using indexes but do not which index will be useful for my query. This query is slow down when users direclty pass page number like page numbers 2500,5000

SELECT candidate.candidate_id AS candidateID,
       candidate.candidate_id AS exportID,
       candidate.is_hot AS isHot,
       candidate.date_modified AS dateModifiedSort,
       candidate.date_created AS dateCreatedSort,
       candidate.candidate_id AS candidate_id,
       attachment_id,
       IF(candidate_joborder_submitted.candidate_joborder_id, 1, 0) AS submitted,
       IF(attachment_id, 1, 0) AS attachmentPresent,
       candidate.first_name AS firstName,
       candidate.last_name AS lastName,
       candidate.key_skills AS keySkills,
       DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
       candidate.email1 AS email1,
       candidate.phone_home AS phoneHome
 FROM
       candidate
       LEFT JOIN attachment 
       ON candidate.candidate_id = attachment.data_item_id
       AND attachment.data_item_type = 100
       LEFT JOIN candidate_joborder AS candidate_joborder_submitted
       ON candidate_joborder_submitted.candidate_id = candidate.candidate_id
       AND candidate_joborder_submitted.status >= 400
       AND candidate_joborder_submitted.site_id = 1
       AND candidate_joborder_submitted.status != 650 
       LEFT JOIN saved_list_entry
       ON saved_list_entry.data_item_type = 100
       AND saved_list_entry.data_item_id = candidate.candidate_id
       AND saved_list_entry.site_id = 1
       WHERE candidate.site_id = 1
       ORDER BY candidate.email1 DESC
       LIMIT 119985, 15

EXPLAIN QUERY

Take a look and the concept of late row lookup: see:explainextended.com/2009/10/23/… — Siebe Jongebloed
– Siebe Jongebloed, Commented May 25, 2021 at 18:12
Skipping 119985 rows first, just to read the next 15 ones is going to be slow. — The Impaler
– The Impaler, Commented May 25, 2021 at 18:12
@TheImpaler, OP has patience. Has already read 119985 rows, 15 at a time. — jarlh
– jarlh, Commented May 25, 2021 at 18:16

DRapp · Accepted Answer · 2021-05-25 19:25:43Z

1

Could not quite make out exact components to your indexes. However, would suggest the following. The specific multiple column indexes on the tables should help performance.

Table                  Index
Candidate             ( site_id, email1, candidate_id )
Attachment            ( data_item_id, data_item_type, attachment_id )
candidate_jobOrder    ( candidate_id, site_id, status )
saved_list_entry      ( data_item_id, site_id, data_item_type )

Slightly rewrite of your query with shorter table alias names and readability. Also added the join on same site_id as candidate where applicable vs hard-coded = 1 in the joins. Also, for querying, you should always do table.column (or alias.column) for those who follow you, or need clarification that do not know your tables. I implied "attachment_id" was from your attachment table (aliased as "a")

SELECT 
        c.candidate_id AS candidateID,
        c.candidate_id AS exportID,
        c.is_hot AS isHot,
        c.date_modified AS dateModifiedSort,
        c.date_created AS dateCreatedSort,
        c.candidate_id AS candidate_id,
        a.attachment_id,
        IF(cjo.candidate_joborder_id, 1, 0) AS submitted,
        IF( a.attachment_id, 1, 0) AS attachmentPresent,
        c.first_name AS firstName,
        c.last_name AS lastName,
        c.key_skills AS keySkills,
        DATE_FORMAT(c.date_modified, '%m-%d-%y') AS dateModified,
        c.email1 AS email1,
        c.phone_home AS phoneHome
    FROM
        candidate c
            LEFT JOIN attachment a
                ON c.candidate_id = a.data_item_id
                AND a.data_item_type = 100
            LEFT JOIN candidate_joborder AS cjo
                ON c.candidate_id = cjo.candidate_id
                AND c.site_id = cjo.site_id
                AND cjo.status >= 400
                AND cjo.status != 650 
            LEFT JOIN saved_list_entry sle
                ON c.candidate_id = sle.data_item_id
                AND c.site_id = sle.site_id 
                AND sle.data_item_type = 100
    WHERE
        c.site_id = 1
    ORDER BY
        c.email1 DESC
    LIMIT
        119985, 15

answered May 25, 2021 at 19:25

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ashish Nishad Over a year ago

Love you 3000 times. Thank you so much. You are like a pro man. Please tell me how you know that which columns should be index ? could you guide me to decide which columns should be in index and what steps are needed for this.

Ashish Nishad Over a year ago

I have another query for count the rows but it is also slowing down.

DRapp Over a year ago

@AshishNishad, create another post with that question and that query, others (and I) can look at and help there too. BTW, did this answer offer significant improvement in query performance for you.

Ashish Nishad Over a year ago

yes your answer is best. I'm not able to create another post because stackoveflow don't allow me. I am stuck in count query taking very long time

DRapp Over a year ago

@AshishNishad, Not allowing to post a question does not make sense. Are you getting some error? I know you need to post the question title and it searches for possible matches before allowing to keep re-posting things already answered. But after filling in the details / body, you should still be able to submit the question. May need to go to a moderator if a problem otherwise.

|

Rick James · Accepted Answer · 2021-05-25 23:02:24Z

0

LIMIT 119985, 15

The query must do all the work to get the rows, then skip over 119985 rows one by one, before delivering the desired 15.

Does this come from a UI and someone did Next Next Next Next Next Next Next Next Next Next Next Next Next Next Next ... enough times to step over 7999 pages?!

The LEFT JOIN to sle is totally useless; remove it. (This will speed it up some.)

Indexes:

c:  INDEX(site_id, email1)   -- (in that order)
a:  INDEX(data_item_id, data_item_type)  -- (either order is ok)
cjo:  INDEX(candidate_id, site_id, status, candidate_joborder_id)

Replace

        IF(cjo.candidate_joborder_id, 1, 0) AS submitted,
    (and)
        LEFT JOIN candidate_joborder AS cjo
            ON c.candidate_id = cjo.candidate_id
            AND c.site_id = cjo.site_id
            AND cjo.status >= 400
            AND cjo.status != 650

with

    ( SELECT IF(cjo.candidate_joborder_id, 1, 0)
         FROM candidate_joborder AS cjo
            ON c.candidate_id = cjo.candidate_id
            AND c.site_id = cjo.site_id
            AND cjo.status >= 400
            AND cjo.status != 650 
    ) AS submitted,
(and)
    no LEFT JOIN

answered May 25, 2021 at 23:02

Rick James

144k15 gold badges144 silver badges254 bronze badges

2 Comments

Ashish Nishad Over a year ago

There is an option in UI for enter page number so user can enter page number or next next page and user enter page 8000 then this limit and offset value in query.

Ashish Nishad Over a year ago

with your query, its taking 15 rows (75.948 s)

Collectives™ on Stack Overflow

Mysql limit offset slow down with large offset value

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related