Zend_Db_Select is relatively good at its job. However,
if you are performing complex queries requiring joins or
sub-selects, it can often be fairly naive.
The only real answer is to write your own SQL;
Zend_Db does not require the usage of
Zend_Db_Select, so providing your own, tuned
SQL select statements is a perfectly legitimate approach,
Run EXPLAIN on your queries, and test a variety of
approaches until you can reliably hit your indices in the most
performant way -- and then hardcode the SQL as a class property
or constant.
If the SQL requires variable arguments, provide placeholders in
the SQL, and utilize a combination of
vsprintf() and array_map() to
inject the values into the SQL:
// $adapter is the DB adapter. In Zend_Db_Table, retrieve
// it using $this->getAdapter().
$sql = vsprintf(
self::SELECT_FOO,
array_map(array($adapter, 'quoteInto'), $values)
);