Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

OFFSET-FETCH is slow - how to optimize

I am using the following query to implement server-side pagination (10000 and 1000 are parameters in my query):

SELECT Name, Id, Color
FROM MyTable
WHERE Color = 'red'
ORDER BY Id,Name DESC
OFFSET 10000 ROWS 
FETCH NEXT 1000 ROWS ONLY

However, this query takes over 4 minutes if select/from/where results in about a million records.

I have an index on both Id and Name column.

I was wondering how this can be furthered optimized.

like image 867
havij Avatar asked Feb 01 '26 06:02

havij


1 Answers

Answering my own question.

There were some nice suggestions in the comments, but the winner (by a considerable margin) was to add the following index suggested by @DanGuzman:

CREATE unique nonclustered index idx_MyTable_Color_Id_Name ON dbo.MyTable(Color, Id, Name DESC);
like image 150
havij Avatar answered Feb 04 '26 01:02

havij



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!