I’ve got this large query that’s bogging things down on my site and I think I’ve exhausted my optimization abilities on it!
What I was trying to achieve was to fetch the products for a category, along with ALL of the information that is required for the on-page display, so as to get it all in one and avoid any iterative queries. One-to-many relationships were resolved by liberal use of GROUP_CONCAT. The goal was for overall speed but I’m not sure I’ve managed that!
The query is below, with the EXPLAIN statement below that. Do you think there’s any improvements I can make? Or am I trying to do too much in this one query? It takes about 4 seconds usually.
All the tables are MyISAM (a couple of of them have FULLTEXT indexes). I’ve tried changing them to INNODB but didn’t see any performance increase. In terms of table size the products table has about 100,000 rows, a similar amount for the images table. They’re the main big ones.
Not sure if the query and the explain are sufficient to allow understanding of what I’m doing. Let me know if I need to post more info.
SELECT SQL_CALC_FOUND_ROWS
p.productID, p.prodName, p.price,
GROUP_CONCAT(DISTINCT pic.fileName ORDER BY pic.index) pictureList,
COUNT(DISTINCT pic.picID) pic_count,
GROUP_CONCAT(DISTINCT 'caption_',pic.index,':',pic.caption ORDER BY pic.index) captionList,
s.deliveryTime,
tax.taxRate,
COUNT(v.videoID) videoCount,
COUNT(DISTINCT review.reviewId) AS review_count, AVG(review.rating) AS avg_review,
GROUP_CONCAT(DISTINCT 'pid:',freeProd.productID,';name:',freeProd.name,';price:',freeProd.price, ';image:',freeProductPic.fileName,';' SEPARATOR '#') freeProds,
GROUP_CONCAT(DISTINCT doc.docFile) docFiles
FROM
products AS p
JOIN product_category AS pcat ON p.productID=pcat.productID
LEFT JOIN tax ON p.taxCode=tax.taxCode
LEFT JOIN productSupplier AS s ON p.supplierID=s.supplierID
LEFT JOIN images AS pic ON p.productID=pic.productID
LEFT JOIN videos AS v ON p.productID=v.productID
LEFT JOIN productReviews AS rev ON p.productID=rev.productID AND review.status=1
LEFT JOIN freeProduct AS fp ON p.productID = fp.productID
LEFT JOIN tProducts AS freeProd ON fp.freeProductID=freeProd.productID
LEFT JOIN images AS freeProductPic ON freeProd.productID=freeProductPic.productID AND freeProductPic.index=1
LEFT JOIN product_productDocuments AS pdoc ON p.productID=pdoc.productID
LEFT JOIN productDocuments AS doc ON pdoc.docID=doc.docID
WHERE
p.isForSale=1
AND
pcat.catId=6615
GROUP BY p.productID
ORDER BY price ASC
LIMIT 0, 100
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY pcat ref unique_index,catId,productID unique_index 4 const 35 Using index; Using temporary; Using filesort
1 PRIMARY p eq_ref PRIMARY,isForSale,prodID_isForSale PRIMARY 4 mydb.pcat.productID 1 Using where
1 PRIMARY tax eq_ref PRIMARY PRIMARY 7 mydb.p.taxCode 1
1 PRIMARY s ref supplierId supplierId 13 mydb.p.supplierID 1
1 PRIMARY pic ref productID,prodID_index productID 4 mydb.p.productID 1
1 PRIMARY v ref productID productID 5 mydb.p.productID 3
1 PRIMARY review ref status,productID,prodID_status prodID_status 7 mydb.pcat.productID,const 5
1 PRIMARY fp ref unique_index,productID unique_index 8 mydb.p.productID 1
1 PRIMARY freeProd eq_ref PRIMARY,prodID_isForSale PRIMARY 4 mydb.fp.freeProductID 1
1 PRIMARY freePic ref relProdId,prodID_index productID 4 mydb.freeProd.productID 1
1 PRIMARY pdoc ref unique_index,productID unique_index 9 mydb.p.productID 1 Using index
1 PRIMARY doc eq_ref PRIMARY PRIMARY 4 mydb.pdoc.docID 1