Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table has two indexes; each one with a single segment, one in asc order and one in desc order.

Apart from the wtf'ness of having an index for every field in every table, it got me thinking - is there any advantage for single-segment indexes to having two indexes with the same index segments, but one in desc and one in asc? Is there anything to be gained, or would a modern DBMS simple use the asc index and start from the end and work its way backwards if required?

share|improve this question
add comment

3 Answers 3

up vote 8 down vote accepted

I'd be surprised to hear of a modern database that can't do reverse order index scans.

share|improve this answer
add comment

Yes, there is a noticeable performance hit (FB 2.5) on a large table when not using a descending index for i.e.:

select first 1 * 
from mytable 
where pk_id >= 200000 
order by pk_id desc

This query is used to find the previous record, based on the value of the primary key field "pk_id" (Integer).

share|improve this answer
add comment

Although Firebird indexes are in theory bidirectional, the engine doesn't actually use the bidirectionality because the reverse direction is not trustworthy because of the write order of pages: when an index page is split, the links between pages are rewritten, if this interleaves with a reverse read it might read a link that is still pointing to the old index page instead of the newly added page, causing it to skip index entries. This is explained in Firebird for the Database Expert: Episode 3 - On Disk Consistency.

So as the bidirectionality of the index is not guaranteed, Firebird only reads an index in its declared direction (either ascending or descending). Now as to why your database has all these indexes, I'd assume that either the person designing the database didn't know what he was doing, or he assumed that adding these indexes would making sorting on any column faster.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.