# This query is choking the server. How can I improve it?

Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof!

The MySQL Processes list shows that as the number of requests for the queries increases, the time to completion also increases... It reaches to a point where queries just lock down.

As per the web host, there is some problem in the query design, but I am unable to figure it out.

SELECT DISTINCT pet.*,ownrs.id AS ownrunique, ownrs.super,
AS noOfCom,
(SELECT count(*) from petfb WHERE pet.id=petfb.petid AND (feedback=1 OR feedback=100))
AS newLikes,
(SELECT count(*) from petfb WHERE pet.id=petfb.petid AND (feedback=5 OR feedback=6))
AS newDisLikes,
ownrs.name
FROM pet, ownrs
WHERE 1=1 AND pet.ownerID=ownrs.uniqueID AND catid=21
ORDER BY  date   DESC  LIMIT 0,10


EXPLAIN:

id  select_type         table       type    possible_keys   key         key_len ref                 rows    Extra
1   PRIMARY             pet         ref     catid           catid       4       const               4758    Using where; Using temporary; Using filesort
1   PRIMARY             ownrs       eq_ref  uniqueID        uniqueID    52      petdb.pet.ownerID   1
4   DEPENDENT SUBQUERY  petfb       ref     petid,feedback  petid       4       petdb.pet.id        11      Using where
3   DEPENDENT SUBQUERY  petfb       ref     petid,feedback  petid       4       petdb.pet.id        11      Using where
2   DEPENDENT SUBQUERY  comments    ref     petid           petid       4       petdb.pet.id        1       Using index


DESCRIBES of the 4 tables (linked to avoid lengthy post).

EDIT 1

Based on Bohemian & a1ex07 replies, I am now using the following query. Everything is working OK but the noOfCom is being calculated wrongly for the pets that have >0 comments - it just equals to the total number of feedbacks from the petfb table for that particular pet, not the actual no of comments. For the pets that have no comments, it shows correct as zero. What could be wrong here?

SELECT pet.*, ownrs.uniqueID AS ownrunique, ownrs.diamond, COUNT( comments.id ) AS noOfCom,
SUM( feedback =1 OR feedback =100 ) AS newLikes,
SUM( feedback =5 OR feedback =6 ) AS newDisLikes
FROM pet
JOIN ownrs ON pet.ownerID = ownrs.uniqueID
LEFT JOIN petfb ON pet.id = petfb.petid
WHERE catid =13
GROUP BY pet.id
ORDER BY DATE DESC
LIMIT 0 , 10


EDIT 2

pet indexes:

Keyname     Type        Unique  Packed  Column          Cardinality     Collation   Null    Comment
PRIMARY     BTREE       Yes     No      id              40499           A
petindex    BTREE       Yes     No      detail (200)    40499           A
catid       BTREE       No      No      catid           62              A
date        BTREE       No      No      date            40499           A
detail      FULLTEXT    No      No      detail          1


petfb indexes:

Keyname     Type    Unique  Packed  Column      Cardinality Collation   Null    Comment
PRIMARY     BTREE   Yes     No      id          1222897     A
petid       BTREE   No      No      petid       71935       A
feedback    BTREE   No      No      feedback    18          A
deviceid    BTREE   No      No      deviceid    55586       A
ownrid      BTREE   No      No      ownrid      671         A


EDIT 3 - SHOW CREATE TABLE petfb

CREATE TABLE petfb (
id int(11) NOT NULL AUTO_INCREMENT,
petid int(11) NOT NULL,
feedback int(11) NOT NULL COMMENT '1=Like 100 = Hidden Like  5=Inappropriate  6=Dislike  7=Duplicate  8=Wrong Type',
fbdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ownrid varchar(50) NOT NULL,
deviceid varchar(50) NOT NULL,
sugcatid int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
KEY petid (petid),
KEY feedback (feedback),
KEY deviceid (deviceid),
KEY ownrid (ownrid)
) ENGINE=InnoDB AUTO_INCREMENT=1215274 DEFAULT CHARSET=latin1

-
I would add an index on (catID, Date, id) –  ypercube Jun 18 '12 at 15:39
And probably on (petid, feedback) on the petfb table. –  ypercube Jun 18 '12 at 15:46
@ypercube: all those are already indexed. Post has been edited to reflect the same. –  BufferStack Jun 18 '12 at 16:09
@ΒufferStack: No, your linked code shows no compound index. –  ypercube Jun 18 '12 at 16:25
@ypercube: not in the linked code. I just made EDIT 2 in the question. Perhaps, a refresh is required at your end. –  BufferStack Jun 18 '12 at 16:29

I believe you need to do COUNT( DISTINCT comments.id ), not COUNT( comments.id ) in your last query .

Update. Assuming petfb.id is a primary key in petfb,

select pet.*, ownrs.id AS ownrunique, ownrs.super,
count(distinct case when feedback=1 OR feedback=100 then petfb.id end ) AS newLikes,
count(distinct case when feedback=5 OR feedback=6 then petfb.id end) AS newDisLikes
from pet
join ownrs on pet.ownerID=ownrs.uniqueID
Left join petfb on pet.id=petfb.petid and petfb.feedback in (1,5,6,100)
group by pet.id
ORDER BY date DESC LIMIT 0,10

-
Yes.. that indeed solved the issue! Thanks! BTW, do you think the new designed query will significantly improve the performance? (PS: Can't vote up, repo<15!) –  BufferStack Jun 18 '12 at 13:37
I think it should. At least you eliminated 1 subquery . I'd also change join condition a bit : LEFT JOIN petfb ON pet.id = petfb.petid AND petfb.feedback in (1,5,6,100). It won't hurt in any case, but may improve performance. You can always check performance with explain: Explain [your query] and see the difference –  a1ex07 Jun 18 '12 at 13:50
I applied the new query.. but there seems not much improvement. The queries are getting in state 'Writing to net' as shown in WHM with >25 secs time. What would that mean? –  BufferStack Jun 18 '12 at 14:04
Also, I would like to add that the newLikes & newDisLikes are also being shown wrong by some weird margins. For ex, for an actual likes of 5, it is showing 10 & in for some other pet actual likes of 20 are being shown as must as 494! I believe something is being multiplied here. Please see. –  BufferStack Jun 18 '12 at 14:24
If you see writing to net, it usually means that either query returns a huge amount of data(in your case you have limit, so I guess you may have blob column) or there is a network issue . –  a1ex07 Jun 18 '12 at 14:26

ALTER TABLE pet
(catid, date) ;

ALTER TABLE petfb
(petid, feedback) ;


Then try this query:

SELECT pet.*
, ownrs.id AS ownrunique
, ownrs.super

, ( SELECT COUNT(*)
) AS noOfCom

, ( SELECT MAX(comments.comDate)     --- Unfortunately, you need
FROM comments                    --- 2 subqueries if you want
WHERE pet.id = comments.petid    --- 2 results from a table

, ( SELECT COUNT(*)
FROM petfb
WHERE pet.id = petfb.petid
AND feedback IN (1, 100)
) AS newLikes

, ( SELECT COUNT(*)
FROM petfb
WHERE pet.id = petfb.petid
AND feedback IN (5,10)
) AS newDisLikes

, ownrs.name
FROM
( SELECT pet.*
FROM pet
WHERE catid = 21
ORDER BY date DESC
LIMIT 0, 10
) AS pet
INNER JOIN
ownrs
ON ownrs.uniqueID = pet.ownerID
ORDER BY p.date ;

-
Getting this implemented into the script is turning out to be quite a complex task, as the query is made by joining multiple parts based on different conditions. It's late night here. I will try it with a fresh mind in morning (about 12 hours from now) & update you then. Thanks. –  BufferStack Jun 18 '12 at 18:09
Hi... Yes it does work & I also see that it doesn't require temp tables! But I cannot test it on live server until query can also support sort my comments. And for that I tried some changes, but it gives an expected error - #1241 - Operand should contain 1 column(s). The is the edit. Please guide how to add this most efficiently. Also please note that I have removed the last line - it seemed that was not required as results are already sorted. –  BufferStack Jun 19 '12 at 6:05
See my edit. if you add too many subqueries for a table, and it becomes slow, you should start considering using @Erwin's solution. With the LIMIT 10 you have, I'd use this one. –  ypercube Jun 19 '12 at 7:09
You'll probably want to add another index on comments (petid, comDate) if this addition slows the query. –  ypercube Jun 19 '12 at 7:09
And don't delete the final ORDER BY p.date. If you want an ordering specify it. You only have 10 rows so it's not going to be slow. Why base the correctness of the result on a assumption of how things will work? –  ypercube Jun 19 '12 at 7:13

Rewrite each of your 3 subqueries as a SELECT COUNT/SUM FROM ... GROUP BY :

SELECT comments.petid, count(*) AS noOfCom from comments GROUP BY comments.petid

SELECT petfb.petid, count(*) from
(SELECT petfb.petid FROM petfb WHERE (feedback=1 OR feedback=100)) AS petlikes
GROUP BY petlikes.petid

SELECT petfb.petid, count(*) from
(SELECT petfb.petid WHERE (feedback=5 OR feedback=6)) AS petdislikes
GROUP BY petdislikes.petid


Then LEFT OUTER JOIN these to your basic pet table.

SELECT * FROM
( (pet JOIN owner ON ...)
LEFT OUTER JOIN commentsquery ON ...
LEFT OUTER JOIN likesquery ON ...
LEFT OUTER JOIN dislikesquery ON ... )
ORDER BY ... [LIMIT ...]


I don't know by heart whether MySQL supports this syntax, but I think it's your only chance of a better-performing evaluation strategy.

EDIT

Unless the performance problems derive from lack of indexes or some such, of course (thought of that while driving home and the foregoing was already posted).

-
Do you mean if MySQL supports subqueries (derived tables). Of course it does. –  ypercube Jun 18 '12 at 15:16
Not sure why you made the 2nd the 3rd as nested while you let the 1st without nesting. –  ypercube Jun 18 '12 at 15:17
No, I meant the JOIN ... ON ... and LEFT OUTER JOIN ... ON ... I don't particularly follow the level of conformance and/or the additional extensions that any given SQL engine has vis-a-vis the standard. For one reason or other, I'd expect those constructs to be available mostly in the engines that also have NATURAL JOIN, EXCEPT, etc ... (and those may not exactly be the majority). –  Erwin Smout Jun 18 '12 at 17:19
You're right about the query nesting in the FEEDBACK queries. The nesting isn't needed. –  Erwin Smout Jun 18 '12 at 17:20
OK then. That's how I'd written the query if there wasn't the LIMIT on the pet table. (and to be honest I'd try both your and my version, just to be safe). One never knows with MySQL and various optimizations were added in some versions so it's version specific as well. –  ypercube Jun 18 '12 at 17:23