[CDBI] Make CDBI go fast

Michael G Schwern schwern at pobox.com
Wed Mar 21 21:42:02 GMT 2007


Brad Bowman wrote:
>> 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?

I don't know if it does anything sneaky, like only the left side of a
quicksort.  That would be cool but somehow I doubt it.


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

*snip*

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

Postgres might be able to do it, given its general flexibility.  It allows you
to index expressions and using WHERE clauses already.  Unfortunately only
indexing the first N doesn't help as soon as someone asks for the next N.
Still, it might help if most folks don't ask for the next page.

One thing which can be done in MySQL is to pre-sort the table.
"ALTER TABLE foo ORDER BY this, that"  That way it has to do less work when
ordering in that particular order with those particular columns.
Unfortunately this covers only one search, but if you set it to the default
search it helps.  Also it does not stay in order, new rows are inserted
willy-nilly.




More information about the ClassDBI mailing list