4

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

SHOW FULL COLUMNS FROM `mytable`

This query might take a few clock cycles to execute (in the Kohana profiler it's actually the slowest of all queries ran in my current app).

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

Luke
  • 20,878
  • 35
  • 119
  • 178

4 Answers4

7

biakaveron answered my question with a comment so I can't except the correct answer.

Taken from Wouters answer on the official Kohana forums (where biakaveron pointed to), this is the correct answer:

It's very easy, $table_columns is a big array with a lot of info, but actually only very little of this info 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')
);
Luke
  • 20,878
  • 35
  • 119
  • 178
  • I've ticked this as the correct answer since it answers the question in the best possible way, whether it's useful or not. – Luke Feb 06 '11 at 00:19
1

There isn't too much overhead when that query gets executed; though you can cache them / skip the process by defining them manually (if that is really what you want override the $_table_columns in your models, though I don't see how much time you can save doing it - it's worth trying).

I proposed a caching alternative for list_columns() but it got denied as it really isn't that much of a bottleneck: http://dev.kohanaframework.org/issues/2848

Kemo
  • 6,942
  • 3
  • 32
  • 39
  • The reason I brought it up is when I used the profiler and ran a half dozen queries for a single request, the SHOW FULL COLUMNS query was the slowest. – Luke Feb 06 '11 at 00:17
1

do not forget underscore:

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 full column info as an array:

var_export($ORM->list_columns());
Sinan Eldem
  • 5,564
  • 3
  • 36
  • 37
0

Not sure how the kohana team said 'show full columns' runs as fast reading from cache for all cases. Query cache is a bottleneck on mysql for our workload due to . So we had to turn it off.

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

Proof that show full columns is the most run query https://www.dropbox.com/s/zn0pbiogt774ne4/Screenshot%202015-02-17%2018.56.21.png?dl=0

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

And the top offending queries (> 100ms) sorted by query count.

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

tven
  • 547
  • 6
  • 18