Menu
In most of the tables having date column, we "would generally" query for the recent information.
Is it a good idea to index a date column "generally" in descending order?
544
The order of the columns in a composite index does matter on how a query against a table will use it or not. A query will use a composite index only if the where clause of the query has at least the leading/left most columns of the index in it.
Primary key columns are typically great for indexing because they are unique and are often used to lookup rows.
Selectivity of the individual columns in a composite index does not matter when picking the order.
What is Descending indexes? A descending index is an index in which the InnoDB stores the entries in descending order and and the optimizer will choose this index when descending order is requested in the query ,which is more efficient for queries with ORDER BY clauses and it need not use a filesort operation.
Not familiar with Oracle's internals, but here's my understanding of how it works with Postgres:
Indexes are clustered for all intents and purposes. So if you they're ordered asc and new rows are always added towards the end of it (e.g. created_at, updated_at, billed_at, etc.), your new rows will be appended (or nearly so) rather than prepended (leading to disk page splits). This is faster.
Your query planner will happily read an index in reverse order. So if it's a single column index, either works -- use the most natural when it comes to how new rows are inserted in your use-case.
Where an index ordered in reverse order may become interesting, is when you've a multi-column index. Say, (id, created_at desc) in an audit log table. It's actually a bad example, but here's the point: if you're ordering by id, created_at desc, the index will be used as is.
57
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
