[CDBI] Make CDBI go fast

[Michael G Schwern]

Perrin Harkins wrote:
> On 2/15/07, Michael G Schwern <schwern at gmail.com> wrote:
>> In reality its much worse because searches often involve REGEXP (in
>> order to do a proper word search rather than substring) which is much
>> slower than LIKE and/or MySQL's full text search engine (which isn't
>> that great) and lots of joins and the fields to sort by are user
>> selected and difficult to index.
> 
> 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*.


>> MySQL doesn't use indexes to do ORDER BY when it has both ASC and DESC
>> anyway.
> 
> 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?