SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    monitormensch oerdec's Avatar
    Join Date
    Sep 2004
    Location
    Hamburg
    Posts
    706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question paging and performance

    Hello all,

    I´m currently working on a shop system. The database design was created by the customer and another developer who is gone now. To get a product out of the database nine different tables have to be querried. To get all products of a category two more are involved. My problem: Paging - it's a performance nightmare. If you have any tips how to do it more efficiently please tell me.

    To get all products of a category I have to:

    1. Get all product IDs of a certain category.
    2. Get product data + language data for each product.
    3. Get all sets for each product. A product can have one or more sets.
    4. Get all articles for each set. A set can have one or more articles.
    5. Get language data for each article.
    6. Get language data + price data for each set.
    7. Assign articles to set.
    8. Assign sets to product.
    9. Return products.


    Then for paging I do something like:

    Code PHP:
    $total_pages   = ceil($total_records / $per_page);
    $current_page  = ($current_page > $total_pages) ? $total_pages : $current_page;
    $start         = $per_page * ($current_page - 1);

    That means I have to run the procedure explained above twice. First to get the total number and then again with LIMIT $start, $per_page.

    OK, how can I improve performance ... beside optimising database design/queries and using MySQL Query Cache?

    I know there´s this FOUND_ROWS() function - but I have absolutely now idea how to use it in this case.

    Additional info:

    My products model class looks basically like this:

    Code PHP:
    class Products {
     
      // get all products of a certain category
      private function getProductsByCategory() {
        $products = $_product->getProducts(...);
        if ($products !== FALSE) {
          foreach ($products as $num_p => $product) {
            $product_data = $this->getProduct(...);
            if ($product_data !== FALSE) {
              $products[$num_p] = $product_data;
            }
            else {
              unset($products[$num_p]);
            }
          }
        }
        return $products;
      }
     
      // get a product
      private function getProduct(...) {
        $product = $_product->getProductData(...);
        if ($product !== FALSE) {
          $language_data = $_product->getProductLanguageData(...);
          if (!empty($language_data)) {
            $sets = $_sets->getSetsByProduct($product['id']);
            if ($sets !== FALSE) {
              foreach ($sets as $num_s => $set) {
                $articles = $_articles->getArticlesBySet($set['id']);
                if ($articles !== FALSE) {
                  foreach ($articles as $num_a => $article) {
                    $language_data = $_articles->getArticleLanguageData($article['id']);
                    if ($language_data !== FALSE) {
                      $articles[$num_a]['name'] = $language_data['name'];
                      // ...
                    }
                    else {
                      unset($sets[$num_s]);
                      break;
                    }
                  }
     
                  if (isset($sets[$num_s])) {
                    $price_data    = $_sets->getSetPriceData($set['id']);
                    $language_data = $_sets->getSetLanguageData($set['id'], $language_id);
                    if (!empty($price_data) && !empty($language_data)) {
                      $sets[$num_s]['price']      = $price_data['price'];
                      $sets[$num_s]['name']       = $language_data['name'];
                      $sets[$num_s]['articles']   = $articles;
                      // ...
                    }
                  }
                }
              }
              $product['sets']  = array_values($sets);
              $product['price'] = // calculated from set prices
              // ...
            }
          }
        }
        return $product;
      }
    }

    ... The output looks like the following. I replaced the values by the according table names.

    Code:
    Array
    (
        [0] => Array
            (
                [id]          => products.id
                [price]       => (calculated)
                [name]        => product_multilingual_data.name
                [sets] => Array
                    (
                        [0] => Array
                            (
                                [id]          => sets.id
                                [price]       => set_prices.price
                                [name]        => set_multilingual_data.name
                                [articles] => Array
                                    (
                                        [0] => Array
                                            (
                                                [id]          => articles.id
                                                [name]        => article_multilingual_data.name
                                             )
                                        [1] => Array
                                            (
                                                ... more articles of this set
                                             )
                        [1] => Array
                            (
                            ... more sets of this product
                            )
        [1] => Array
            (
            ... more products in this category
            )

    oerdec

  2. #2
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Normally, a single query can retrieve all the required data from a set of tables if you could write proper 'Join' queries. Are you sure, you need to execute all of those different queries instead of a single join query?

  3. #3
    monitormensch oerdec's Avatar
    Join Date
    Sep 2004
    Location
    Hamburg
    Posts
    706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Kailash,

    There are actually three join tables and I`d love to do this with just one SQL statement. But there are some calculations and checks necessary which can only be done with PHP.

    For example:

    A set can have one or more articles. If there are no articles at all - remove the set. If there`s no language data for just one article - remove the set too ... and then, the price of a product is calculated from all set prices. For every set there´s a calculation rule stored in the database ... and so on.

  4. #4
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, its pretty tricky. So I should assume that there is no way you can find the total number of results with a single query (like finding the total number of products)? If no, have you tried doing it with stored procedures which should let you move some of the calculations from PHP into mysql itself?

  5. #5
    monitormensch oerdec's Avatar
    Join Date
    Sep 2004
    Location
    Hamburg
    Posts
    706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As far as I know stored procedures are available since MySQL 5 but I have to use version 4 at the moment. Maybe this will change in the future - after the launch.

    I'm using the query cache. After some time it´s hopefully improving the performance. From the manual:
    The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.
    Another idea:

    I would like to save the HTML output in files, e.g. one file with all products of a category. Instead of doing all that query/calculation stuff again and again I could simply send a static file to the browser... or maybe just parts of a "page" come from static files.

    The problem: Whenever an article, set or product has changed, the cache file is obsolote. I haven't found a way yet to manage this.

  6. #6
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you add/edit products via some sort of admin application, then set it up so that whenever an edit or addition is performed, that cache is updated as well.

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your admin section need only delete stale cache files when something changes. Then the next page view will regenerate the cache file.

    Using object buffering it's pretty easy to capture and cache pieces of content for retrieval later. You just need to keep track of cache files that may exist to be removed by the CMS.

    The ideal solution though would be to improve the SQL. Even if some work needs to be done in PHP I'm pretty certain the queries could be massively improved.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •