[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.

Brad

-- 
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