4
For default, relational databases create indices using increasingly ordered binary tree structures. But there is the possibility of creating it in a decreasing way too.
My question is whether (and when) it is recommended to use it. For example:
SELECT nome
FROM pessoa
WHERE data_cadastro >= '2014-01-01'
ORDER BY data_cadastro DESC
In the above SQL, considering the WHERE and ORDER BY, a decreasing index in the column data_cadastro would improve performance?
2
Imagine an index in a column of a grouped table:
CREATE TABLE mytable (
pk INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)
The col1 index keeps ordered col1 values along with the line references.
Once the table is grouped, the references to the lines are actually the values of pk. They are also sorted within each value of col1.
This means that the index is sorted into (col1, pk), and this query:
SELECT col1, pk
FROM mytable
ORDER BY
col1, pk
No need for ordination!
If we create the index as follows:
CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)
then col1 values will be downgraded, but pk values within each col1 value will be downgraded.
This means that the following query:
SELECT col1, pk
FROM mytable
ORDER BY
col1, pk DESC
Can be served by ix_mytable_col1_desc but not by ix_mytable_col1.
"Does not need sorting" can lead incorrectly to the presumption that making a select without order by will return the values ordered by the index What is not guaranteed! In fact the engine will even try to sort according to the definition of order by. It will only take a lot less work when doing it
Yes, yes! But that will just take less work. You hardly make a select without order by today if it’s for screen display...unless you use frameworks that already sort automatically
1
Depends on your need. By default the indexes are created in ascending order because, in general, the values (usually ids) are generated increasing. This way the new (larger) values go to the end of the "list". This decreases the chance of fragmentation of the index.
Example: Suppose you have a table where the Id is autoident starting at 1 and adding +1 to each new record. You define this column as PK clustered but change the order of the index to descending.
This will force page splits once each new record will force table reordering.
A plausible scenario to use descending ordering could be where you make few Inserts but usually make many selects on the newer values
Example: You have a record of meetings you attended and create an index ordered by the date. Eventually a new meeting is inserted and you usually just use a select to return to last week’s meetings.
As always in SQL everything depends. When in doubt make a test Acid. Try select, Insert, delete, update operations on the table in question using the sorting of the two forms and try to measure the performance differences. This is really the only way to figure out how to improve the performance of an index
Browser other questions tagged sql database performance índices
You are not signed in. Login or sign up in order to post.
Why did they try to close that question ?
– gmsantos
@gmsantos The reason chosen was "Based on Opinions"
– user7261
Well I put an answer trying to explain the basics of how sorting will affect selects and Inserts. I believe that this is within the scope of the OP question and is not based on "personal opinion" but on facts of how the indexes work
– jean
Exactly, @jean. I believe you were wrong to vote to close.
– gmsantos