Re: [CDBI] Make CDBI go fast

Perrin Harkins wrote:
> 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.

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! :)