[CDBI] Make CDBI go fast
Michael G Schwern
schwern at gmail.com
Fri Feb 16 15:57:12 GMT 2007
Perrin Harkins wrote:
> 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.
Yep, we're thinking the same thing.
I'm sure they thought the same thing about the full-text indexing but there's a small pile of hacks in the code to work around its inefficiencies. Apparently it doesn't do so good on words shorter than 4 characters and they have people searching for things like "us hwy 1". So I might experiment with my own word index.
>> > 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.
I see. How do you invert a date? MAX_DATE - date? (Oh no! Y10K bug! :)
More information about the ClassDBI