SQL creates high server load?

markman641 · 10-10-2012, 12:59 AM

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.

Quote:

$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";

Old Pedant · 10-10-2012, 01:13 AM

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?

Old Pedant · 10-10-2012, 01:16 AM

And will you really have the same offerid in the same category more than once in the offers table???

markman641 · 10-10-2012, 03:13 AM

Quote:

Originally Posted by Old Pedant

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.

markman641 · 10-10-2012, 03:18 AM

Quote:

Originally Posted by Old Pedant

And will you really have the same offerid in the same category more than once in the offers table???

nope! offerid is unique

Old Pedant · 10-10-2012, 05:37 AM

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.

markman641 · 10-10-2012, 05:45 AM

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

Old Pedant · 10-10-2012, 07:23 AM

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.

Old Pedant · 10-10-2012, 07:24 AM

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.

Old Pedant · 10-10-2012, 07:42 AM

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.

markman641 · 10-10-2012, 10:17 PM

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?

Old Pedant · 10-11-2012, 12:00 AM

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.

Old Pedant · 10-11-2012, 12:08 AM

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.

markman641 · 10-12-2012, 05:58 AM

Quote:

Originally Posted by Old Pedant

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

guelphdad · 10-12-2012, 12:38 PM

Quote:

Originally Posted by markman641

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.