SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2008
    Location
    Gloucestershire, UK
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How Many MySQL Queries is Too Many?

    Hello,

    I'm putting together a dynamically generated Javascript expanding menu, which uses nested unordered lists, for the CMS for an ecommerce application.

    The obvious way (to me) of populating the menu is to nest queries and while loops within each other, which is easy enough but I'm a bit worried about issuing that many database requests (it as the site rows it could easily exceed 100 just to make that menu).

    Am I ok just getting the information I need for each part of the menu by nesting queries and while loops, even it means making loads of requests, or would it be better to get all the data in a couple of queries at the start of the script to create a multi-dimensional array and then generate the menu using foreach loops?

    Cheers,

    Jon

  2. #2
    Non-Member
    Join Date
    Oct 2007
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no such thing as too many. The important thing is to do 2 things:

    1) Make your data layer lazy - by this I mean don't hit the DB until your code actually needs what you'll be querying. If you're a beginner (or even if you're a pro I still recommend this route), you can use an ORM (such as what ships with Django, Rails, etc.). This will handle your code and make it as optimized as can be without as much effort on your end.

    2) Even more important is caching. You'll want to cache the results of the menu's queries, and only refresh them every day (or when cache is cleared manually). A program like Joomla, or Drupal for PHP has this functionality built in, but you'll want to be sure to use mem-caching (in-memory caching which is very fast).

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by orokusaki View Post
    There is no such thing as too many.
    Better to keep the number of db queries as low as possible, because they are relatively slow on the server. Where possible, use a single query with a join rather than one query and then loop through the resultset issuing another query for each result.
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2008
    Location
    Gloucestershire, UK
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies.

    The way the menu is structured is that I have a table of brands (I need the id and brand name), within those brands are products ranges (I need the id, name, brand id), and within the ranges are products so to access them I'll have a product table and an "id association" table (each row has type id, brand id, range id, product id) which will be accessed in a single query using a join.

    So, if I loop through the brands, and nest a while loop calling each range within the brand, and then loop through the products within the range, I'm going to be making loads of requests.

    By the way, I'm coding from scratch as opposed to using Joomla etc.

    Cheers,

    Jon

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jonpugh114 View Post
    So, if I loop through the brands, and nest a while loop calling each range within the brand, and then loop through the products within the range, I'm going to be making loads of requests.
    so don't do it that way

    write a single join query -- much more efficient

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I sense from your description that you’d be better off with a single Join query. Make sure you have proper indexes in place etc.

    It’s always good to minimize the number of queries. However, number (of queries) alone is not always the best measure to compare performance. Complexity of a query and design of the table it runs against is also an important factor. A single complicated query is sometimes a bigger bottleneck than 10 simpler queries combined. I guess it’s best to profile and analyze all different mixes of these queries and work out the one that gives the most performance.

  7. #7
    SitePoint Zealot beeXtreem's Avatar
    Join Date
    Jan 2006
    Location
    Nigeria
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please, how do I cache a query without using joomla,Zend Frameworks,Drupal, etc.?
    Also, what queries (examples) are best cached, and which should not?
    JC Rocks!!

  8. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Caching can give a big performance boost, but it's not a silver bullet that gives you license to be lazy with the underlying code. You should not be issuing lots and lots of queries to build a menu. Use a JOIN query as r937 stated.

    From a usability perspective I would avoid having hundreds of items all available from a single menu. It's hard to understand and navigate.

    The main nav is probably worth caching, because it'll be displayed pretty much unchanged on most pages, but it shouldn't take 5 seconds to generate the first time.

  9. #9
    Non-Member
    Join Date
    Oct 2007
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "not a silver bullet" <- very true.

    Where to cache:

    Things like lists of menu items, full article archives, etc. are best cached. Closed comment areas too. Things you cannot cache are bank balances, account settings, etc. Things that need updating, and are not widely viewed. A bank balance is widely viewed, but each person's is different, so what I mean is like an article that will be viewed 150 times.

    How to cache:

    Unfortunately, caching is one of those things that can be considered an inconvenient performance formality, and shouldn't be done until you're sure you're ready (not making drastic changes to your application any time really soon). This is not always true however because with some solutions, things are pretty automatic, meaning that you can make changes without having to worry too much. On "how to cache", there are far too many ways to do it to demonstrate in full here, but the basic idea is to just use mem-cache in whatever you do (if there is the option to) vs file-system cache, or middleware server cache.

  10. #10
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    write a single join query -- much more efficient
    Not always true... sometimes you can do multiple selects on primary keys, and it goes faster.

    This also gives the advantage of being able to cache 'objects', so in the long run you will do less queries to the database (~95% less on a big dynamic dating site I worked on, in one cases, ~5000 queries a sec turned to ~20).

    As orokusaki described, even if your menu needs 20 queries to be generated the first time, you cache it for a week, and that's 20 queries you run a week for it.

    But, unlike orokusaki described, you can also cache the 'things that need updating', just make sure to invalidate the cache when you update them (or update the cache).

    Also, do some research, sometimes, the memory used in caching some pages that are almost never seen, could be better spent on caching something more frequently used. (memcache does some of this work for you)

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Vali View Post
    ... sometimes you can do multiple selects on primary keys, and it goes faster.
    for the problem stated in post #1 of this thread, i still stand behind my advice that a single query will be optimum

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Jun 2008
    Location
    Gloucestershire, UK
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem I'm having is sorting and displaying the data once I have it. If I was using nested queries and while loops I could easily get this:

    HTML Code:
    <ul>
    <li><a href="edit_brand.php?b_id=1">Brand One</a>
         <ul>
         <li><a href="edit_range.php?r_id=4">Range Four</a>
                  <ul>
                  <li><a href="edit_product.php?p_id=15">Product Fifteen</a></li>
                  <li><a href="edit_product.php?p_id=20">Product Twenty</a></li>
                  </ul>
          </li>
          </ul>
    </li>
    </ul>
    I'm struggling to see how I could this structure without first putting the query results into arrays (and I'm struggling to get my head around that too).

    Cheers,

    Jon

  13. #13
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think not just a good query but a good optimized database is also required to perform good and fast working.

    I do agree with some one who wrote that some time a single but long query is not useful but 10 simple queries works some time.

    what you say guys.

    Noddy


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
  •