Why does OFF SET slow down SQL?

Question

I have the following SQL, generated by Entity Framework 6.

SELECT 
    [Project1].[C1] AS [C1], 
    [Project1].[ID] AS [ID], 
    [Project1].[CAMPO1] AS [CAMPO1], 
    [Project1].[CAMPO2] AS [CAMPO2], 
    [Project1].[CAMPO3] AS [CAMPO3], 
    [Project1].[CAMPO4] AS [CAMPO4], 
    [Project1].[CAMPO5] AS [CAMPO5], 
    [Project1].[CAMPO6] AS [CAMPO6], 
    [Project1].[TOTALCLICKS] AS [TOTALCLICKS]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[CAMPO1] AS [CAMPO1], 
        [Extent1].[CAMPO2] AS [CAMPO2], 
        [Extent1].[CAMPO3] AS [CAMPO3], 
        [Extent1].[CAMPO4] AS [CAMPO4], 
        [Extent1].[CAMPO5] AS [CAMPO5], 
        [Extent1].[CAMPO6] AS [CAMPO6], 
        [Extent1].[TOTALCLICKS] AS [TOTALCLICKS], 
        1 AS [C1]
        FROM  [VIDEO] AS [Extent1]
        INNER JOIN [VIDEO_CATEGORIA] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID_VIDEO]
        WHERE [Extent2].[ID_CATEGORIA] = 25
    )  AS [Project1]
    ORDER BY [Project1].[TOTALCLICKS] DESC
    OFFSET 273910 ROWS FETCH NEXT 130 ROWS ONLY 

Basically it is a relationship between the video table and video_category.

The access plan informs that it uses the index in video_categoria by id_video. What’s good.

Then it goes to video table and uses the primair id key. Here it spends 98% of the time.

Then it loops and uses the video index of total_clicks to sort and catch the 130 of OFF SET.

This query takes more than three minutes, what I do not understand is that if I put the OFF SET starting from 1 it runs in two seconds (OFFSET 1 ROWS FETCH NEXT 130 ROWS ONLY )

Why is that difference?

Answer 1

It is normal to decrease performance, having that the research needs to count the first offset + limit. The higher the value, the longer the search will take.

The search does not go straight to OFFSET because, first, the record can be different in size, and second, there can be no gaps in deleted records. He needs to check and count every record again.

That code in mysql can help you, being id the primary key, you can use this to increase speed:

SELECT  t.*
FROM    (
        SELECT  id
        FROM    tabelaX
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    tabelaX t
ON      t.id = q.id

read more in (English):

Mysql ORDER BY / LIMIT performance: late Row lookups