PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

SQL generated with Zend_Db_Select s not hitting my indexes; how can I make it better?

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.

Write your own tuned SQL

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

Zend Framework