sql-serversqliteindexing

Does database compound index order matter if the first column is only queried with an equal operation?


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.


Solution

  • 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>?)