How to lower the number of MySQL queries using Object Oriented Code

Question

Let's take a simple example: I have products. Products can be in 1 or many Categories, and also in 1 or many Channels.

So I have an object for Product, Category and Channel, as well as their mapper and factory classes.

My approach #1 is to be 100% decoupled; the ProductFactory just create an instance of a Product. And inside the Product classe, I have lazy instanciation method that call the Category & Channel Factory and store the objet as needed.

That's great for the product Details Page, but when it come to a product listing, my query count gets really high, as I must do 2 extra query per product, and let's face it: when I display the list of the products in the category #2, it's completly insane to instanciate 25 time the very same category for each product.

My approach #2 is to couple things a little bit; the ProductFactory make some JOIN and fetch all the informations of the Category and Channel, and instanciate them. IMO that's as much nasty as the first solution, because it the query count get low, I just run bigger query, so in the background, it changes nothing.

My approach #3 is to keep thing decoupled, but have no chainable automatisation; I would first loop over all the product, and make an array of all the Category and all the Channel, control duplicate ID, and then instanciate all of them. That seem nice, but the code is no more chainable. ( Ie.: I can't do $product->getCategory() to get the list of the category where the product is stored.)

What approach do you suggest ?

One big query isn't equivalent to several smaller queries - each query adds overhead, so you are actually better off if you can combine them. — Sam Dufel
– Sam Dufel, Commented Aug 25, 2011 at 15:56
You're right, but I was more thinking about the fact that I'm fetching 25 time the very same information... — FMaz008
– FMaz008, Commented Aug 25, 2011 at 15:58

Galen · Accepted Answer · 2011-08-25 16:16:20Z

1

What if you keep an array in the format

CATEGORY_ID => CATEGORY_OBJECT

Then when you need to assign a category you can check that array first. For example:

$page_categories = array();

foreach( $products as $product ) {
    if ( isset( $page_categories[$product->category_id] ) ) {
        $product->category =  $page_categories[$product->category_id];
    }
    else {
        $product->category = $page_categories[$product->category_id] = new Category( $product->category_id );
    }
}

answered Aug 25, 2011 at 16:16

Galen

30.2k9 gold badges75 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to lower the number of MySQL queries using Object Oriented Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related