My table with columns A, B, C, D has an index on A,B.
For query SELECT A,B,C,D, From MyTable WHERE A=? AND B=? ORDER BY C DESC I expect SQLite to use the index where A and B match, then sort by C if necessary. The query executes slow and EXPLAIN QUERY PLAN revealed SQLite only uses partial search by A, then scan. If I remove ORDER BY the whole index is used.
Is this the expected? Can I hint SQLite to use the whole index?
Because the column c is not indexed, the database must retrieve all rows that meet the conditions specified for columns a and b, and then sort them. When using indexes, the database needs to read complete rows from the disk based on the matched index entries. These rows are stored in different locations on the disk, requiring random I/O. On the other hand, full table scans use sequential I/O, which is much faster than random I/O. Therefore, when loading more than 10%-30% of the rows in a table at once, most databases will opt for a full table scan. This threshold is not fixed and depends on various factors.