Beware of MySQL View Filtering in Your PHP Code

We love MySQL VIEW feature since it allows us to keep a lot of JOIN statements outside the code and in the DBA land. What this means is that whenever we need a JOIN statement in the code, we can instead define a VIEW that can be easily reviewed by our DBA for correctness and efficiencies. This works really well as it keeps our code really clean and our DBA can hammer the VIEWS without getting involved in the code. But there is a big caveat: filtering the VIEW can have a severe performance penalty.

For example, say you have a view such as the following:

CREATE VIEW view_best_customers AS
SELECT C.*, PH.total_amount, PH.rank
FROM customers as C LEFT JOIN purchase_history as PH ON C.customer_id = PH.customer_ID
WHERE PH.total_amount > 1000 ORDER BY PH.rank DESC;

Now if you use this view in your PHP code as follows:

   // GOOD
   $stmt   = "SELECT * from view_best_customer";
   $result = mysqli_query($link, $stmt);

You are just fine as you are using the VIEW as it is supposed to be used. However, what if your code looks like the following:

   // BAD
   $stmt   = "SELECT * from view_best_customer WHERE C.email LIKE '%@gmail.com'";
   $result = mysqli_query($link, $stmt);

Here you are in trouble. You are “filtering” the view using a column. This forces MySQL to create a temporary table every time you execute this query! Well, not exactly every time if you call the same query again and again. But most of the time, when you execute this query, it creates a temporary table and thus it costs a lot of execution time.

So do not filter your MySQL VIEWs and instead use them as-is.

Category: Concepts, PHP, Performance, Programming | Tags: