Question: assuming I have an Orders
table with an index (UserId, CreatedTime)
, does it matter if I add DESC
to CreatedTime
if I want to list orders by a single user only?
For example:
SELECT *
FROM Orders -- Also potentially have row limit
WHERE UserId = @UserId
ORDER BY CreatedTime DESC;
What I do not understand is that, if the answer is yes, why can't the DB simply start at the bottom and go up since it knows the range of @UserId
already?
Also I work with both SQL Server and SQLite so I would appreciate to know if the answer would be different for each DBMS.
I still do not really understand SQL Server indexes - ascending or descending, what difference does it make? and apparently in MongoDB, it doesn't matter (Does order of indexes matter in MongoDB?).
Back to the above query, even if I was to list all users and their corresponding orders in descending CreatedTime
, why can't the database do this:
I even asked AI and it simply told me it's slower to fetch from bottom up without further explanation even when I tried to press it for one.
In SQLite, the index will be used whether you sort by CreatedTime ascending or descending.
Given:
create table orders (
user_id integer,
created_time integer
);
create index orders_index on orders (user_id, created_time);
The query plans for both sorts are identical:
sqlite> explain query plan select * from orders where user_id = 1 order by created_time;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index (user_id=?)
sqlite> explain query plan select * from orders where user_id = 1 order by created_time desc;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index (user_id=?)
If instead you're doing a range query on user_id and then sorting by user_id, created_time
, an additional temporary table will be used only if created_time
is sorted in descending order:
sqlite> explain query plan select * from orders where user_id > 1 order by user_id, created_time;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index (user_id>?)
sqlite> explain query plan select * from orders where user_id > 1 order by user_id, created_time desc;
QUERY PLAN
|--SEARCH orders USING COVERING INDEX orders_index (user_id>?)
`--USE TEMP B-TREE FOR LAST TERM OF ORDER BY
For this query, you'll need an index for created_time desc
:
create index orders_index_2 on orders (user_id, created_time desc);
=>
sqlite> explain query plan select * from orders where user_id > 1 order by user_id, created_time desc;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_index_2 (user_id>?)