[CDBI] Make CDBI go fast

Brad Bowman list at bereft.net
Wed Mar 21 05:06:49 GMT 2007

> 2) Searches which return large numbers of rows and must be ordered.
> An example of #2 would be:
> SELECT name, address, zip, phone, last_contact FROM   customers WHERE  name
> LIKE '%Smith%'; ORDER BY name asc, last_contact desc LIMIT 11 OFFSET 0
> For an example of "search for everyone named Smith and show me the first 10
> ordered by name and the last time we had contact with them".  This sort of
> search can easily return 100,000 rows which then must be sorted (which
> MySQL does with a temp table and a filesort) in order to know what the
> first 11 are (10 + 1 so we know if there's another page's worth).

Does MySQL sort the entire file to get the first 11?  Do other databases?

Does any database allow a LIMIT $N index?  I mean, maintaining an index
of the top 10 only, updating as the appropriate.  Or is this best done
another way?

> 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.  MySQL doesn't use indexes to do ORDER BY when it has both ASC and
> DESC anyway.  The search code is very hairy to attempt to optimize all
> this.

Sounds like my suggestion wouldn't apply in any case, given the flexibility
of the searches.


After reading books and the like it is best to burn them or throw them away.
                                     -- Hagakure http://bereft.net/hagakure/

More information about the ClassDBI mailing list