MySql: The power of Views (or Better late than never)

Posted on October 24, 2009

2


I sometimes get the sour feeling of “there’s something fundamentally wrong with this! It works, but nevertheless…” It happens in all aspects of life, including programming. Some of (not always!) the times this feeling causes curiosity and energy to strive for a new perspective, new knowledge, new ways of facing the problem. Aspects of inutition and creativity, I guess…

In my current project there’s an awful lot of SQL going on. Lots of connected tables, nestled selects etc. In the preparation for the next level of full text searchability, I stumbled upon the concept of Views in MySql. I can’t believe that I didn’t know this beauty before!

In short, a View is a virtual database table where the content is a result of a underlying sql statement. It can be accessed by normal queries just like any table – a view can build its content depending on tables as well as other views.

For a good introduction of MySql Views, check this Techtopia blogpost out.

Creating a View in PhpMyAdmin is a breeze, there’s a Create View link in the right bottom of any table view:

pma-create-view

I haven’t found any button to retrieving the underlaying View sql though. However, it can be done by using a normal “SHOW CREATE VIEW tablename” sql statement.

The concept of views will ease my further development a lot! I’m glad I did listen to that feeling… 🙂

Advertisements
Posted in: Database