MySQL: Using Views as Performance Improvement Tools

The most basic and most oft-repeated task that a DBA has to accomplish is to look at slow logs and filter out queries that are suboptimal, that consume lots of unnecessary resources and that hence slow down the database server. The most obvious reason for these suboptimal queries is that their authors are generally not that good at writing complex queries. A DBA has a far better chance of writing an optimal query as compared to any other database user, because a DBA has a better understanding of the internals of the database server (query optimizer, etc).

The problem

Normally looking at logs, one would find a host of suboptimal operations that are performed, for example the use of SELECT * when you only need a few columns. The most oft-repeated queries that end up in slow logs are ill-formed joins, and these form the largest percentage of problematic queries that a DBA normally fixes. These types of operations consume too many resources that should otherwise be available for other processing needs of the server.

The solution

The most obvious solution to this problem is limiting access to the database, relieving database users of the task of creating optimal queries. This can be achieved by the use of VIEWs. VIEWs that database users can query just like any other normal table. A DBA can write VIEWs that limit the dataset both horizontally and vertically, ensuring that only the data that is required is queried for. Also, a DBA can write VIEWs that eliminate ill-formed joins.

Some planning is required

But using VIEWs in this manner will require a lot of planning and understanding of the data access patterns. A DBA, together with going through logs to see data access patterns, should also have frequent consultations with the application developers to understand the needs of the application, so that he can design appropriate views.

And finally with VIEWs in place and users confined to querying those VIEWs, the DBA can have a good night sleep without all those nightmares of poorly-formed joins.

  • http://twitter.com/ssmusoke Stephen S. Musoke

    MySQL views are useless for large data sets > 500,000 records in MySQL 5.0. The performance was so bad that we have ported all of our applications to use straight queries.

    MySQL also lacks materialized views (like Oracle) which are essentially tables that are updated on the fly. That would provide a much needed performance boost

  • http://www.ovaistariq.net/ Ovais Tariq

    Stephen, which view processing algorithm were you using,. MERGE or TEMPTABLE,,. TEMPTABLE has very poor performance,.

  • http://www.ovaistariq.net/ Ovais Tariq

    You can also have a look at flexviews which is a materialized views toolkit for MySQL

  • Mubashir

    either u use temtable or merge..both are slow… coz the views hasn’t support of index

  • http://www.ovaistariq.net/ Ovais Tariq

    I would like to correct you,. there is a big difference between how both the algorithms work:

    1. Merge algorithm executes the view definition as a normal SQL query and hence, all the scenarios that apply to a regular SQL query apply to such a view.
    2. Temptable algorithm, on the other hand, copies the result of the view, inserts it in a temporary table and then applies the filtering, which means that no indexes will be used during the filtering,.

    So in fact the right thing to way would be “temptable does not use index”,.

  • Oakley vault

    The eyewear brand companies Oakley vault sunglasses mainly for women with a couple of designs in the actual mens category. Known because of its colorful and unique sets, Oakley vault are easily recognizable using the embossed logo on the actual temples. The exquisite design is from the frames range from Oakley sunglasses outlet to classic aviator tones, with pastel and earthy colored lenses. The variety of colors the brand offers could be compared with the mood from the Cheap Oakley vault.

  • Oakley vault

    The eyewear brand companies Oakley vault sunglasses mainly for women with a couple of designs in the actual mens category. Known because of its colorful and unique sets, Oakley vault are easily recognizable using the embossed logo on the actual temples. The exquisite design is from the frames range from Oakley sunglasses outlet to classic aviator tones, with pastel and earthy colored lenses. The variety of colors the brand offers could be compared with the mood from the Cheap Oakley vault.

  • Ray Ban Sunglasses

    Ray Ban
    UK
    have produced its initial appearance about 80years
    in the past and become a hitting item inside the market speedily. These
    Ray Ban
    Sunglasses
    are committed to blocking glares and
    several other dangerous rays. Due to their distinctive traits and effectiveness
    in filtering dangerous rays, raybanuk2012.net
    grows to be widely accepted and famous all over the globe.