[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.

Whaddya know!

    13.1.4. CREATE INDEX Syntax
    ...
    An index_col_name specification can end with ASC or DESC.

Yay!

    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.

Boo!

MySQL giveth and MySQL TAKETH AWAY!

Of course, Postgres can index on full blown expressions and with WHERE clauses.



More information about the ClassDBI mailing list