Menu
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?
486
Discussion: Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).
Introduction to SQLite ORDER BY clause The ORDER BY clause comes after the FROM clause. It allows you to sort the result set based on one or more columns in ascending or descending order. In this syntax, you place the column name by which you want to sort after the ORDER BY clause followed by the ASC or DESC keyword.
A table may have multiple indexes. Whenever you create an index, SQLite creates a B-tree structure to hold the index data. The index contains data from the columns that you specify in the index and the corresponding rowid value. This helps SQLite quickly locate the row based on the values of the indexed columns.
An INTEGER PRIMARY KEY becomes the actual key used in the B-tree that stores your table. So no index is required for efficient operation.
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
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
