Stopping SHOW COLUMNS/SHOW CREATE TABLE

Yii 1.1 General Discussion
1

Having concerns over why my site was running so many DB queries per page load, I decided to turn on the profiling logger, and was pretty shocked at what I found. Nearly half of my DB calls are generated by Yii, in the form of SHOW COLUMNS and SHOW CREATE TABLE commands; over half of my DB execution time is generated by them. I have never previously encountered a web application where such a thing is necessary, which leaves me with two questions; A.) Why is it doing this? And B.) How do I make it stop?

2

A ) This is how ActiveRecord retrieves the schema to power it’s attributes.

B ) You can’t stop it but you can enable caching of the DB schema so it only does it once per model until that cache expires. Check out: http://www.yiiframework.com/doc/api/1.1/CDbConnection#schemaCacheID-detail You’ll need to enable the cache component also.

3

Okay, at least A is answered. B just needs some liberal application of engineering. Not the first Yii component I’ve had to deconstruct.

So then, what format is it expecting the schema to be in? An array would certainly be easy, I could drop that right into each model file. The responsibility for database awareness lies solely on the developer, not the computer, and I’ll be more than happy to make sure my site operates accordingly.

4

You just enable caching and schema cache works automatically. My live servers cache schema for 1 hour :)

5

The caching still performs that SHOW COLUMNS and SHOW CREATE TABLE but it then caches the result for x period of time, so it doesn’t need to do those queries again. If you want to provide a static ORM config then maybe you should be looking at an alternative to an AR implementation.

6

Well the thing is, we don’t have caching; we’re on one of those $8-a-month shared hosting plans. But, with a little bit of engineering and a whole lot of actually caring how efficient my code is, I’ve still achieved page load speeds up to 30 times faster than a comparable site running on dedicated hardware with memcached.

So yes, a static configuration is definitely what I’m aiming for. And chances are it’s not that hard; there’s probably one function I can override to redirect it to local, static information. The hard part is finding it.

Edit: Okay, apparently it’s not that simple, because the information is bounced around through a terrifying number of arrays and objects. Doing what exactly is unclear but unimportant, I just know I don’t need it. New idea; what functions attempt to use this data, so I can replace those?

7

If you are still in development or can’t cache then use


jQuery('document').ready(function(){

	jQuery('.yiiLog td').each(function(){

		

		if(jQuery(this).html().search("Querying SQL: SHOW COLUMNS") != -1 ||

		   jQuery(this).html().search("Querying SQL: SHOW CREATE")  != -1 )

		{

			jQuery(this).closest('tr').addClass('hide');

		}

	});

});