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

Posted on

Question :

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,
(SELECT count(*) from comments WHERE pet.id=comments.petid) 
    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 comments ON pet.id = comments.petid
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

Answer :

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 comments.id) AS noOfCom, 
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 comments on pet.id=comments.petid 
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

First add the following indices:

ALTER TABLE pet
  ADD INDEX catid_date_index 
      (catid, date) ;

ALTER TABLE petfb
  ADD INDEX petid_feedback_index 
      (petid, feedback) ;

Then try this query:

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

     , ( SELECT COUNT(*) 
         FROM comments 
         WHERE pet.id = comments.petid  
       ) 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
       ) AS lastCom                       --- (comments)

     , ( 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 ;

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).

Leave a Reply

Your email address will not be published. Required fields are marked *