abidibo.net

Just Open Source

MySQL, bad preformance of INFORMATION_SCHEMA queries

cache mysql performance php

Recently I've discovered a quite big perfomance problem when running INFORMATION_SCHEMA queries on mysql.

In particular I found that on some systems (some mysql versions) a query like

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'db_table'

can take up to 0.1s to run!

Clearly such time interval is not acceptable, and I think it happens only with some versions of mysql because I've run some tests on my local machine and on the server where this site is running obtaining a result of 0.0007 seconds, we are talking about a difference of two orders of magnitude!

I've not investigated more about which mysql versions are affected and if there are any patches, I'll do it in the next future, but for now I've adopted another solution.

The (current) solution

The problem for me was that Jeff (php framework) performs such query every time a model object is instantiated, in order to get information about the object's fields.

So the solution for me was to cache the query result for each model. I've done this using the powerful cache class which jeff provides, and which is included in the following gist

How to use the cache class

So using this cache class is quite simple. Imagine we have a function called getTableStructure($table) which returns the structure of a table running the query above, all we have to do is to replace

$result = getTableStructure($table);

with

$cache = new dataCache();
$caching_time = 3600; //seconds
if(!$result = $cache->get('table_structure', $table, $caching_time)) {
  $result = getTableStructure($table);
  $cache->save($result);
}

And we've done! Basically the code inside the if condition is executed only if the get method of the cache object returns false, and it returns false only if the caching file is not present or the caching time has expired.

Adjust the caching time to fit your needs, I've found that this way the charging time of some pages is really improved, also of values of seconds in some cases!

Considerations

Generally it would be a good practice to cache the result of all complex queries!

The problem here is to set the right expiration time, but even in this case there some cases where caching a query result could be a problem, for example if we update our site contents often, the changes will be available only when the caching time expires.

But do not forget that almost in every project we control the action of insertion and modification of contents in the backend, so actually it is possible to delete the caching files (which are gruped by a group_name) when a modification or an insertion is performed, so that the first time our page is visited a new cache file is generated, and the problem disappears.

Subscribe to abidibo.net!

Special offer!

If you subscribe to this blog within the 7 of february 2016, you'll receive a discount code of 1€ that you can use to buy my first e-book "Your Smartwatch Loves Tasker", available for purchase here on abidibo.net at the price of 3.79€!


If you want to stay up to date with new contents published on this blog, then just enter your email address, and you will receive blog updates! You can set you preferences and decide to receive emails only when articles are posted regarding a precise topic.

I promise, you'll never receive spam or advertising of any kind from this subscription, just content updates.

* indicates required

abidibo.net topics

Comments are welcome!

blog comments powered by Disqus

Your Smartwatch Loves Tasker!

Your Smartwatch Loves Tasker!

Now available for purchase!

Featured