>I've noted that the documentation says that whether your index is ASC or >DESC matters, but it's not clear to me either why it should or exactly >what the implications are. > >Boiled down[#], I've got a table MYTABLE with an integer column MYCOLUMN >with an index on it, and I'm looking at queries like > >(a) select COUNT(*) from MYTABLE where MYCOLUMN < 2 >(b) select COUNT(*) from MYTABLE where MYCOLUMN > 2 > >The vast majority of rows in the table have MYCOLUMN = 2, so I'm looking >at these queries to return very small numbers very quickly. (MYCOLUMN = >2 means "this object is OK", and I'm looking to find the objects that >are not OK.) > >If the index is ASC then > >(a) is fast >(b) is very slow (although it claims to be using the index, according to >the plan, it's doing as much work as a table scan, according to the stats)
Decided that a value in the middle should mean OK, is - well - not the greatest idea. Probably you also started out with a limited number of options and then adding other values to have other meanings made sense once upon a time. I would recommend to change OK from being 2 to being something like 9999 (a higher value than you expect to need for other purposes). If that's not easy, I would consider adding the column MyColumnNegative, populated through a trigger and set to the negative equivalent of MyColumn. Then a) would still be fast, and if you changed b) to (b) select COUNT(*) from MYTABLE where MYCOLUMNNEGATIVE < -2 then that should also be fast. Other options that you could consider at least include using computed indexes (though I've never tried them) and adding a triggerpopulated new table(s) - either containing references to records that are not OK or a table containing the sums (when records are deleted, you insert a row containing -1, when records are inserted, you insert a row containing +1, when a row is modified, you may end up inserting one row with +1 and another with -1, occationally you sum data and delete old sums). All in all, you have quite a few options you can consider to circumvent your problem while awaiting Firebird 3 with histograms (haven't checked alpha 1 yet, so I'm not certain they are included). HTH, Set
