LOL glad I could help. What Sybase and MS SQL server do depend on whether or not you declare your index as:
CREATE INDEX user_userid ON user( userid )
CREATE INDEX user_userid ON user( userid DESC )
For an ORDER BY DESC:
The index is actually read in reverse. This isn't as efficient as the index being ordered decending and then being read sequentially forward.
Index is decending, read from start to finish.
Sybase and MS SQL Server have the concept of a 'clustered' index (Oracle has an analagous structure called a grouped table). Basically it sorts your data rows by a particular key (or keys) so that the rows do not have to be sorted in memory.
It also improves heavily on BETWEEN clauses or grouping.
For instance, if you were designing a forum system in Syb/MS SQL/Oracle you would do something like this:
So the thread rows are actually physically sorted by forumid, lastpost desc. Meaning it will arrage threads on disk in the same physical location as the default vBulletin thread view (shows you the threads in the forum with the last post highest). It saves a significant amount of I/O to have the threads pre-sorted.
CREATE TABLE thread(
threadid INT IDENTITY, -- ident is same as auto_increment
forumid INT NOT NULL REFERENCES forum( forumid ),
lastpost INT NOT NULL
CREATE CLUSTERED INDEX thread_forumid_lastpost ON ( forumid, lastpost DESC )
CREATE TABLE post(
postid INT IDENTITY,
CREATE CLUSTERED INDEX post_threadid_postid ON post( threadid, postid )
If you are really interested in index binary tree layout you also save a level of binary tree since the clustered index leaf nodes are the data rows! So your indexes are 1 level smaller which is more efficient to search and store in memory.
The reason why I included postid in the post_threadid_postid index was that without it, there would have to be a sort (because new rows are not necessarily added to the end of the table) to ORDER BY postid ASC.