If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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.

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.

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.

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.

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.

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?

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 01: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.

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.