Reducing the number of database queries in a complex custom app

Question

I inherited an e-commerce software project written in PHP. As I was inspecting the codebase, I found a lot of SQL statements all over the code. There are a lot of classes like Product, Category, User, Customer, etc. and every class has a lot of database queries.

I didn't know how to treat this situation and decided to count the total queries of a single page visit. I encapsulated the MySQL query function and increased a counter.

I was a little shocked at the result. To visit the index-page alone, there were 1633 (!) MySQL select queries executed. Listing the products of a category triggered almost 2000 queries.

I piped the queries into a text file to analyze them. Over 90% are single select statements of maybe one or two values. Now what should I do to clean up this mess? What is your advice? I enabled caching at the MySQL server. Loading the page takes about 490ms.

Additional detail

For example, there is a class called Product. Inside this class there are 8 single small SQL select statements.

When you now open the category listing to display the products, the original programmer used one select statement to get get a list of the needed products and then created a product object for each of them.

Let's say this result gives us 20 products:

select id from products where price <= 10;

then he iterates through the results and creates a product object for every entry:

$qresult = query("select id from products where price <= 10");
$products = array();
foreach ($qresult as $prod) {
  $products[] = new Product($prod['id']);
}

This alone generates 20 * 8 SQL queries just for the products. And the same method is used for other classes too (User, Customer, Category and so on).

Some Time Ago

Now, after some weeks/months have passed, I wanted to share my solutions I did so far.

I could cut down the queries to < ~50 per page visit and reduced the page loading time to under 400ms.

I did it very easily. I tried to identified the hotspots and build a table cache class. Each visit this static class loads the whole table content to memory and each table request from now on will served out of the memory from the static class. Well very dirty and not that nice but it works, is faster, reduces the total queries and spares the server hardware.

I guess we also will throw hardware to this problem als long as the user count increases in that ratio as it did by now.

If we come to the point to replace the application by another, we will definitely head for a database-query-nice solution

thanks all for your advices

Answer 1

Well, for starters the first thing you need to identify lies in the php realm, and is how many of those calls are executed more than once. That alone will cut down your numbers.

Other than that you can do two things.

1. If you have several queries that use the same parameters (say productId, and you bring the product table, the product category table and so forth, you can always join those queries and bring a single result with all you need (or make views of the most common queries joined and work with that)
2. If you have a settings table, the best thing to do is load all the table into memory and read all values from there, so you don't have to go to the DB anytime you need a setting

Answer 2

I think you're right to be concerned. 2,000 queries seems a lot.

You’ve already identified two good reasons for refactoring, the amount of queries and the page response time – both measureable and suitable for refactoring.

Although it is true MySQL has a query cache, and it will usually not query the underlying data each time if it can fulfil the query from the cache there could still be a network cost of talking to MySQL.

Have you thought about saving values to memory or using a session variable?

<?php
session_start();
// store session data
$_SESSION['sharedvalue']= "result of common MySQL query"
?>

< html>
< body>

< ?php
//retrieve session data - now each time you do this it isn't asking MySQL again
echo "Common Value=". $_SESSION['sharedvalue'];
?>

< /body>
< /html>

Another solution would be have your own cache and query common values from that, changing or expiry them for refreshing old data. This would really be dependent on the size of your application and user base.