[CDBI] Make CDBI go fast

Perrin Harkins pharkins at gmail.com
Thu Feb 15 19:40:40 GMT 2007

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

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

> I'm hunting things down, but really the slow query log isn't showing much.  When it is the database its usually because MySQL had to do a filesort.

There are a couple of server tuning parameters you can tweak that may
help.  I expect you know about those, since they're described on the
"order by" optimization page.

> You'd be surprised how long it takes to create 100,000 CDBI objects, inflate their columns, use their accessors, flesh out their groups....

I believe it.  In some cases it probably does another query per object.

> > One thing that I noticed when looking at how our CDBI app performs is
> > the way the database handles are always asked for with a method call,
> > even in places where passing them would be really easy.  Many tiny
> > methods in CDBI need a handle for something, and call db_Main, causing
> > a $dbh ping.  I haven't checked how these add up in the profiler
> > though.
> Hasn't shown up in profiling.

Good, glad to hear it.

- Perrin

More information about the ClassDBI mailing list