Zend Framework and Mysql - very slow

Question

I am creating a web site using php, mysql and zend framework. When I try to run any sql query, page generation jumps to around 0.5 seconds. That's too high. If i turn of sql, page generation is 0.001. The amount of queries I run, doesn't really affect the page generation time (1-10 queries tested). Stays at 0.5 seconds I can't figure out, what I am doing wrong.

I connect to sql in bootstrap:

protected function _initDatabase ()
{
    try
    {
        $config = new Zend_Config_Ini( APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV );
        $db = Zend_Db::factory( $config -> database);
        Zend_DB_Table_Abstract::setDefaultAdapter( $db );
    }
    catch ( Zend_Db_Exception $e )
    {

    }
}

Then I have a simple model

class StandardAccessory extends Zend_DB_Table_Abstract
{
    /**
     * The default table name 
     */
    protected $_name = 'standard_accessory';

    protected $_primary = 'model';

    protected $_sequence = false;
}

And finally, inside my index controller, I just run the find method.

require_once APPLICATION_PATH . '/models/StandardAccessory.php';
    $sa = new StandardAccessory( );
    $stndacc = $sa->find( 'abc' );

All this takes ~0.5 seconds, which is way too long. Any suggestions?

Thanks!

Answer 1

Tips:

• Cache the table metadata. By default, Zend_Db_Table tries to discover metadata about the table each time your table object is instantiated. Use a cache to reduce the number of times it has to do this. Or else hard-code it in your Table class (note: db tables are not models).

• Use EXPLAIN to analyze MySQL's optimization plan. Is it using an index effectively?

  mysql> EXPLAIN SELECT * FROM standard_accessory WHERE model = 'abc';
  

• Use BENCHMARK() to measure the speed of the query, not using PHP. The subquery must return a single column, so be sure to return a non-indexed column so the query has to touch the data instead of just returning an index entry.

  mysql> SELECT BENCHMARK(1000, 
    (SELECT nonindexed_column FROM standard_accessory WHERE model = 'abc'));
  

• Note that Zend_Db_Adapter lazy-loads its db connection when you make the first query. So if there's any slowness in connecting to the MySQL server, it'll happen as you instantiate the Table object (when it queries metadata). Any reason this could take a long time? DNS lookups, perhaps?

Answer 2

Along with the above suggestions I did a very unscientific test and found that the PDO adapter was faster for me in my application (I know mysqli is supposed to be faster but maybe it's the ZF abstraction). I show the results here (the times shown are only good for comparison)

Answer 3

The easiest way to debug this, is to profile your sql queries. you can use Firephp (plugin for firebug) see http://framework.zend.com/manual/en/zend.db.profiler.html#zend.db.profiler.profilers.firebug

another way to speed up things a little is to cache the metadata of your tables. see: http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.metadata.caching