Re: [CDBI] Make CDBI go fast

On 2/15/07, Michael G Schwern <[email protected]> wrote:
> > It sounds like you know what would help there: full text search on
> > this column, pre-processing to turn this into an exact match on a
> > related table, or even using a LIKE with an anchor at the beginning
> > (since 'Smith%' can use an index).
>
> I haven't thought of the pre-processing.  I believe the assumption was that MySQL's full text indexing would take care of it.  Another possibility is to drop support for *foo and only allow foo*.

The kind of pre-processing I had in mind was to split each word into a
row, indexed in a related table.  Full-text indexing is probably
better because it's maintenance-free.

> > If this is important and frequently-used, it may be worth creating
> > artificial sort columns that reverse the sort order of certain fields.
> > Then you can sort the same direction and use the indexes.  You may
> > end up creating many indexes though, so it's not a simple solution.
>
> Do you have an example of this?

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.

- Perrin