[CDBI] Make CDBI go fast
Michael G Schwern
schwern at gmail.com
Fri Feb 16 17:02:10 GMT 2007
Perrin Harkins wrote:
> Not a battle-tested one, but the idea would be to make an inverted
> copy of one column you want to sort by (like a ~column). Then when
> you sort it DESC it's sorting the real column ASC. Kind of a lame
> hack, but you could keep it up to date with a trigger if the logic is
> simple enough.
So I was explaining all this to a conveniently available friend sitting next to me who doesn't know SQL very well. I drew out a table representing the indexes to show how ORDER BY ASC ASC and DESC DESC was fast but ASC DESC wasn't. And she suggested to just make another index column sorted in the opposite direction. Which is what we're doing, but by tricking the database. I started to explain that it wasn't possible to control the indexing like that... and then I remembered this is MySQL and they have all sorts of crazy extensions.
13.1.4. CREATE INDEX Syntax
An index_col_name specification can end with ASC or DESC.
These keywords are allowed for future extensions for specifying ascending or descending
index value storage. Currently, they are parsed but ignored; index values are always
stored in ascending order.
MySQL giveth and MySQL TAKETH AWAY!
Of course, Postgres can index on full blown expressions and with WHERE clauses.
More information about the ClassDBI