Helping Kohana 3 ORM speed up a bit

Question

I noticed that Kohana 3 ORM launches "SHOW FULL COLUMNS" for each of my models when I start using them:

SHOW FULL COLUMNS FROM `mytable` 

This request may require several clock cycles (in the Kohana profile, this is the slowest of all requests running in my current application).

Is there a way to help Kohana 3 ORM speed up by disabling this behavior and explicitly defining the columns in my models?

Answer 1

biakaveron answered my question with a comment, so I cannot exclude the correct answer.

Those taken from Wouters answer in the official Kohana forums (where biakaveron pointed out), this is the correct answer:

It is very simple, $table_columns is a large array with a lot of information, but actually very little of this information is used in ORM.

This will do:

 protected $_table_columns = array( 'id' => array('type'=>'int'), 'name' => array('type'=>'string'), 'allowNull' => array('type'=>'string','null'=>TRUE), 'created' => array('type'=>'int') ); 

Answer 2

There is not too much overhead when executing this request; although you can cache them / skip the process by defining them manually (if this is really what you want to override $_table_columns in your models, although I don’t see how much time you can save, it's worth a try).

I suggested an alternative to caching for list_columns() , but it was refused because it really doesn't hurt that much: http://dev.kohanaframework.org/issues/2848

Answer 3

do not forget to emphasize:

 protected $_table_columns = array( 'id' => array('type'=>'int'), 'name' => array('type'=>'string'), 'allowNull' => array('type'=>'string','null'=>TRUE), 'created' => array('type'=>'int') ); 

This will give you the full info column as an array:

 var_export($ORM->list_columns()); 

Answer 4

Not sure how the kohana team stated that “show full columns” works like a quick read from the cache for all cases. The query cache is the bottleneck in mysql for our workload. Therefore, we had to disable it.

https://blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing

The proof showing the full columns is the most running query https://www.dropbox.com/s/zn0pbiogt774ne4/Screenshot%202015-02-17%2018.56.21.png?dl=0

Proof of temporary tables on disk from the NewRelic mysql plugin. https://www.dropbox.com/s/cwo09sy9qxboeds/Screenshot%202015-02-17%2019.00.19.png?dl=0

And the top offensive requests (> 100 ms) sorted by the number of requests.

https://www.dropbox.com/s/a1kpmkef4jd8uvt/Screenshot%202015-02-17%2018.55.38.png?dl=0