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.