Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post

    SQL creates high server load?

    For some reason, my host yelled at me because one of my SQL queries was creating too much server load. Is the SQL string inefficient and is something causing it to go longer then it should? I have never ran into a problem with it, but I'm guessing now that there are more rows in my "offer" table it may create more stress.

    $sql = "SELECT offers.category, COUNT(DISTINCT SS.offerid) as howmany "
    . " FROM offers LEFT JOIN ( "
    . " SELECT category AS cat, offerid FROM offers "
    . " WHERE countries LIKE '%$country%' "
    . " AND offerid NOT IN (SELECT offerid FROM completed_offers WHERE username='$usr') "
    . " ) AS SS "
    . " ON offers.category = SS.cat "
    . " GROUP BY offers.category ORDER BY offers.category";

  2. Users who have thanked markman641 for this post:

    crewson548 (10-17-2012)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Probably, you are just missing some indexes that are needed to bring the query up to a reasonable speed.

    At a MINIMUM you need indexes on the following table.fields:

    -- completed_offers.username
    -- offers.offerid
    -- offers.category

    But having said that... That query sure looks like it is more complex than is needed!

    Do you really want to return offers.category if the howmany value will be zero?

    And do you really need to use LIKE in that WHERE countries LIKE ...??

    PLEASE don't tell me that your contries field is actually a LIST of countries! Please say it isn't something like Austria,Australia,Belgium ... please?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    And will you really have the same offerid in the same category more than once in the offers table???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    Probably, you are just missing some indexes that are needed to bring the query up to a reasonable speed.

    At a MINIMUM you need indexes on the following table.fields:

    -- completed_offers.username
    -- offers.offerid
    -- offers.category

    But having said that... That query sure looks like it is more complex than is needed!

    Do you really want to return offers.category if the howmany value will be zero?

    And do you really need to use LIKE in that WHERE countries LIKE ...??

    PLEASE don't tell me that your contries field is actually a LIST of countries! Please say it isn't something like Austria,Australia,Belgium ... please?

    Lets tackle these one by one.
    1. updated the following:
    -- completed_offers.username -- Index
    -- offers.offerid -- Unique Index
    -- offers.category -- Index
    2. Basically the purpose of this whole code is to combine all the categories that are alike and count how many for each and put them into a dropdown
    3. My countries are stored as Austria,Australia,Belgium because each row in the 'offers' table has a different set of countries. this was the easiest way.
    Last edited by markman641; 10-10-2012 at 04:48 AM.

  • #5
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    And will you really have the same offerid in the same category more than once in the offers table???
    nope! offerid is unique
    Last edited by markman641; 10-10-2012 at 04:49 AM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Well, the killer is your bad choice of putting multiple countries into one field. "Easiest" is often not the best in database design, and multi-valued fields are one of the biggest no-nos around. You wouldn't get away with that in any even medium scale corporate operation.

    I'm not sure we can fix the performance problem so long as you have that multi-valued field, but we can at least make a stab at it.

    You missed answering one question:
    > Do you really want to return offers.category if the howmany value will be zero?
    though I'm not sure it makes much performance difference.

    *MAYBE* this query will be more efficient than yours. No promises. That countries LIKE is still a killer.

    Code:
    $sql = "SELECT OS.category, IFNULL(SS.thecount, 0) AS howmany "
        . " FROM offers AS OS LEFT JOIN ( "
        .     " SELECT O.category, COUNT(*) AS thecount " 
        .     " FROM offers AS O LEFT JOIN completed_offers AS C"
        .     " ON ( O.offerid = C.offerid AND C.username='$usr' ) "
        .     " WHERE O.countries LIKE '%$country%' "
        .     " AND C.offerid IS NULL "
        .     " GROUP BY O.category ) AS SS "
        . " ON OS.category = SS.category " 
        . " ORDER BY OS.category";
    The main thing I changed was to use a LEFT JOIN instead of you IN(...) to combine offers and completed_offers. If this were SQL Server, either would give you the same efficiency. But with MySQL, IN(...) can sometimes not work very well. The trick here is that even though I'm using a LEFT JOIN, the AND C.offerid IS NULL turns it into a sort of reverse INNER JOIN.

    And since I was then doing that as a LEFT JOIN, it made sense to move the counting "down" one level, thus avoiding the need for the COUNT(DISTINCT ...) (also an expensive operation).

    That's all the tricks I can see, assuming I got the query right, short of normalizing you DB design.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    Wow! That ABSOLUTELY cut the time down! It took it from about 7-10 seconds down to 1-2! You rock! Also, yes. I want to return the offers.category if the howmany value will be zero

    EDIT: I take that back. It didn't work. Take a look at what it SHOULD look like, as apposed to what your new query does:
    After: http://puu.sh/1deKd
    Before: http://puu.sh/1deKK
    Last edited by markman641; 10-10-2012 at 05:48 AM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Ahhh...yes, I see that.

    The problem occurs because, again, your DB isn't normalized. If it were, you would have a separate CATEGORIES table, where each category had a categoryid (INT PRIMARY KEY) and cateogoryname. And then, in your OFFERS table, you would use the categoryid instead of the name. You really should read up on NORMALIZATION. Your lack of it is causing you all sorts of efficiency griefs.

    So lacking that separate CATEGORIES table...

    We could fix it by just changing the first SELECT to SELECT DISTINCT, but that won't be efficient.

    So try this...
    Code:
    $sql = "SELECT OS.category, IFNULL(SS.thecount, 0) AS howmany "
        . " FROM ( SELECT DISTINCT category FROM offers ) AS OS " 
        . " LEFT JOIN ( "
        .     " SELECT O.category, COUNT(*) AS thecount " 
        .     " FROM offers AS O LEFT JOIN completed_offers AS C"
        .     " ON ( O.offerid = C.offerid AND C.username='$usr' ) "
        .     " WHERE O.countries LIKE '%$country%' "
        .     " AND C.offerid IS NULL "
        .     " GROUP BY O.category ) AS SS "
        . " ON OS.category = SS.category " 
        . " ORDER BY OS.category";
    If it's not obvious, SELECT DISTINCT category FROM offers is, essentially, building the missing CATEGORIES table. But it's a much more expensive operation than if you actually had such a table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    markman641 (10-10-2012)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Truly, if your DB were completely normalize with proper indexes, I wouldn't expect this query to take more than 200 milliseconds in MySQL given the apparent number of records you seem to have.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    As I see it you need these tables:
    Code:
    CREATE TABLE Categories (
        catid INT AUTO_INCREMENT PRIMARY KEY,
        catname VARCHAR(100)
    );
    
    CREATE TABLE Countries (
        coid INT AUTO_INCREMENT PRIMARY KEY,
        coname VARCHAR(100)
    );
    
    CREATE TABLE Customers (
        custid INT AUTO_INCREMENT PRIMARY KEY,
        coid INT FOREIGN KEY REFERENCES countries(coid),
        custname VARCHAR(100))
    );
    
    CREATE TABLE Offers (
        offerid INT AUTO_INCREMENT PRIMARY KEY
        catid INT FOREIGN KEY REFERENCES categories(catid),
        offerdetails .... one or more fields to describe the offer ...
    );
    
    CREATE TABLE OffersByCountry (
        offerid INT FOREIGN KEY REFERENCES offers(offerid),
        coid INT FOREIGN KEY REFERENCES countries(coid)
    );
    
    CREATE TABLE CompletedOffers (
        custid INT FOREIGN KEY REFERENCES custormers(custid),
        offerid INT FOREIGN KEY REFERENCES offers(offerid)
    );
    The foreign key reference in magenta there may not be necessary, but it is likely a good idea.

    The syntax there for the foreign keys isn't quite correct for MySQL, but it's a compact form that easy to read for a post like this.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    I don't quite understand how those tables would work. How would i store the list of countries per offer in a country category? If you wanted a new row for each, that would be a LOT of rows. Give me an example of each table with a test row?

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Code:
    Categories
        catid: 1  
        catname: Aardvark Restarints
    
    Countries
        coid: 1  
        coname: Albania
    
        coid: 2
        coname: Austria
    
    Customers
        custid: 1  
        coid: 1 [FK to countries, Albania] 
        custname: Adam
    
    Offers
        offerid: 1  
        catid: 1 [FK to categories, Aardvark Restraints]
        details: Get 2.99 off when you purchase 3 aardvark foot clamps
    
    OffersByCountry:
        offerid: 1
        coid: 1 [Albania]
      
        offerid: 1
        coid: 2 [Austria]
    
    CompletedOffers:
        custid: 1 [Adam]
        offerid: 1 [first offer]
    Yes, you have to have one record per offer/country combination in the OffersByCountry table. SIZE IS NOT AN ISSUE in most database access problems. Properly indexed tables will give lightning performance whether there are 100 or 1000000 records in the table. This is a classic case of a "many-to-many" table.

    A query such as yo were doing in your existing tables would probably be done as:
    Code:
    SELECT C.catname, IFNULL(thecount,0) AS howmany
    FROM Categories AS C
    LEFT JOIN (
        SELECT O.catid, COUNT(*) AS thecount
        FROM Offers AS O 
        INNER JOIN OffersByCountry AS OC 
            ON O.offerid = OC.offerid
        INNER JOIN Countries AS C 
           ON C.coid = OC.coid AND C.coname = 'Albania'
        LEFT JOIN CompletedOffers AS CO
           ON CO.offerid = O.offerid AND CO.custid = 1
        WHERE CO.offerid IS NULL
        GROUP BY O.catid 
        ) AS X
    ON C.catid = X.catid
    ORDER BY C.catnamt
    We could get fancy and pull the country id from the customers table for the given customer, so that we don't need to join to the countries table.

    Hmmm.... I guess that would be
    Code:
    SELECT C.catname, IFNULL(thecount,0) AS howmany
    FROM Categories AS C
    LEFT JOIN (
        SELECT O.catid, COUNT(*) AS thecount
        FROM Offers AS O 
        INNER JOIN OffersByCountry AS OC 
            ON O.offerid = OC.offerid
        INNER JOIN Customers AS CU 
           ON CU.coid = OC.coid AND CU.custid = 1
        LEFT JOIN CompletedOffers AS CO
           ON CO.offerid = O.offerid AND CO.custid = CU.custid
        WHERE CO.offerid IS NULL
        GROUP BY O.catid 
        ) AS X
    ON C.catid = X.catid
    ORDER BY C.catnamt
    Yes, that is neater. Now the country where the customer lives is automatically pulled in.

    THAT QUERY WOULD BE FAST, assuming that all of the foreign keys were themselves indexes.
    Last edited by Old Pedant; 10-11-2012 at 12:04 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • The Following 2 Users Say Thank You to Old Pedant For This Useful Post:

    crewson548 (10-17-2012), markman641 (10-12-2012)

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    So what kind of performance did you get from the query in my post #8?

    I forgot to mention something: A condition that uses LIKE can *NOT* use an index, except in the specialized case of WHERE field LIKE 'xxx%'

    That is, where the only % wild card character is at the end of the string to match via LIKE.

    So your query is FORCING MySQL to do a COMPLETE scan of the entire OFFERS table.

    And that's why it's slow. And why it would be hard to make it faster.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    markman641 (10-12-2012)

  • #14
    Regular Coder
    Join Date
    Jul 2011
    Posts
    272
    Thanks
    63
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    So what kind of performance did you get from the query in my post #8?
    Definitely reduced the time. Half of what it was before. Thanks for all your input man, I'll keep it in mind when I get around to fixing it! :P

  • #15
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by markman641 View Post
    I don't quite understand how those tables would work. How would i store the list of countries per offer in a country category? If you wanted a new row for each, that would be a LOT of rows. Give me an example of each table with a test row?
    This is not a knock at you personally, but I'm always surprised that people get hung up on having a "lot" of rows.

    The important thing is to normalize the data.

    A lot of rows in a table is 50,000,000 and even then that isn't really a lot.

    Properly tuned databases with good indexes for the type of queries being ran, with normalized data and optimized queries are important. The number of rows, significantly less important.

  • Users who have thanked guelphdad for this post:

    crewson548 (10-17-2012)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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