0

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.

havij
  • 1,030
  • 14
  • 29
  • 1
    you need a index on color as well, also try combined indexes – nbk Mar 24 '23 at 21:33
  • Which dbms? "sql" is not a good tag to use ("*tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead.*") https://stackoverflow.com/questions/tagged/sql – Paul Maxwell Mar 25 '23 at 01:12
  • Index on Color column (to aid filtering) and a composite index on Id and Name columns (to aid ordering) could improve performance. – Paul Maxwell Mar 25 '23 at 01:16
  • 1
    If `Id` is the PK or is otherwise unique there is no need to do `ORDER BY Id, Name DESC` - just `ORDER BY Id` will be sufficient. – Dai Mar 25 '23 at 01:40
  • @PaulMaxwell [SQL Server doesn't/can't use two indexes on a base-table like that for `WHERE` _and_ `ORDER BY`](https://stackoverflow.com/a/52010774/159145). An alternative approach will have to be used, such as simply returning the entire unordered resultset to the client. – Dai Mar 25 '23 at 01:42
  • if I had known it was sql-server at the time... you may notice I also asked for the tag "sql" to be improved upon (but do admit I missed the more obvious point about ordering on ID) – Paul Maxwell Mar 25 '23 at 02:00
  • You have indexes but are they used in the query? You need to provide the query plan. An index on color, id, name desc might be able to match your query together with include on all the fields you are selecting. – siggemannen Mar 25 '23 at 09:03
  • 1
    I suggest you try this composite index: `CREATE unique nonclustered index idx_MyTable_Color_Id_Name ON dbo.MyTable(Color, Id, Name DESC);`. I expect that would be used to optimize the `WHERE` and `ORDER BY` clauses. – Dan Guzman Mar 25 '23 at 11:22

1 Answers1

0

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);
havij
  • 1,030
  • 14
  • 29