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
Best Answer
I believe you need to do
COUNT( DISTINCT comments.id )
, notCOUNT( comments.id )
in your last query .Update. Assuming petfb.id is a primary key in petfb,