Sort order for sqlite index

user_159982 0
Viewed 3305

There is sort order when creating index for sqlite.

https://sqlite.org/lang_createindex.html

Each column name or expression can be followed by one of the "ASC" or "DESC" keywords to indicate sort order.

So, there are three options: no-sort, ASC, DESC when creating index. Where is info about how I should use them? I cant find it.

I guess, if I use ASC or DESC in queries, I should add them to index. But should I add them both if they are both in different queries? Or should I just don't set sort order and it will select it itself?

What is a general rule for it?

indexing sorting sqlite
2 Answers

SQLite can scan an index in both directions. For an index on a single column, this means it can be used to sort in both ASC and DESC order.

Reference:

The Idx1 index is scanned from top to bottom (or from bottom to top if "ORDER BY fruit DESC" is used) in order to find the rowids for each item in order by fruit.

But for an index on more than one column, there are more than two directions that one might wish to sort the data. For instance, on a two column index, one might wish to sort via ASC, ASC; ASC, DESC; DESC, DESC; or DESC, ASC order.

If the index was created in ASC, ASC order, then sorting by ASC, ASC or DESC, DESC will be able to fully use the index. But sorting by ASC, DESC and DESC, ASC will not be possible solely using the index. That is why the index order may be specified.

For more info: https://use-the-index-luke.com/sql/sorting-grouping/order-by-asc-desc-nulls-last

user_6276630
Related