[CDBI] Make CDBI go fast
pharkins at gmail.com
Fri Feb 16 04:22:12 GMT 2007
On 2/15/07, Michael G Schwern <schwern at gmail.com> 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
More information about the ClassDBI