Frank Herrman

Finally, a cure for hicups

A little girl mixed up some grandma-wisdom to come up with a lollipop to cure hicups. I don’t know if it works, but if it does I will buy the first ones! Somehow I have hicups too often for too long, but I guess everybody hates hicups. There is a big market for this cure!

(Source: Business Insider)

Materialized Views in MySQL: speed up your view

To speed up the process of a query which joins many tables together you may create a view. This is a type of cached query which acts like a table but is automatically updated by the query which you used to define it.

  CREATE
    OR REPLACE
    VIEW my_happy_view
    AS SELECT a.column1, b.column2 
         FROM table1 a 
         LEFT JOIN table2 b ON a.id=b.table1_id

When you use a very complex query to create your view you may encounter long loading times when selecting data from the view. To solve this, the people from Oracle, Microsoft and other database systems came up with a solution: materialized views.

Materialized views are basically tables which are updated once in a couple of hours. If the data should be real-time available, this is not an option. It is really ment for data warehousing. Unfortunately, MySQL does not support materialized views.

But there is a solution! We can implement something similar ourselves. Just create a PHP file which creates a caching table. In our example we would create a table my_happy_view_cache:

   CREATE TABLE my_happy_view_cache SELECT * FROM my_happy_view

Yes it’s that simple. Then make sure we select data from my_happy_view_cache instead of the original view. To keep the data up-to-date we have to execute the above query reguarly. Simply create a php file (or any other language) which drops the above table before recreating it.

To schedule this file create a shell script which executes ‘php /path/to/yourfile.php’. Put this script in your crontab and execute it as often as is needed. There you have it, your manually created materialized view!

Of course this is still not real-time, but close enough. It will speed up your scripts immensely.