1,105,456 Community Members

improve mysql join query execution time

Member Avatar
ss.jatinmehta
Newbie Poster
1 post since Apr 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I have one join query with 3 tables, which takes lot's of time to execute. I need to reduce execution time i.e., optimize it

QUERY: 
SELECT SQL_CALC_FOUND_ROWS p.*, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating, 0 AS prodgroupdiscount, pi.* , (IF(p.prodname='gold', 10000, 0) + IF(p.prodcode='gold', 10000, 0) + ((MATCH (ps.prodname) AGAINST ('gold')) * 10) + MATCH (ps.prodname,ps.prodcode,ps.proddesc,ps.prodsearchkeywords) AGAINST ('gold')) AS score FROM products p LEFT JOIN product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1) INNER JOIN product_search ps ON p.productid = ps.productid WHERE p.prodvisible = 1 AND (ps.prodcode = 'gold' OR TRUE) AND (MATCH (ps.prodname,ps.prodcode,ps.proddesc,ps.prodsearchkeywords) AGAINST ('gold')) ORDER BY score DESC LIMIT 20

EXECUTION TIME: 2.5000+ seconds

TABLES DATA:
products: 31,000 records
product_images: 92,000 records
product_search: 57,000 records

EXPLAIN COMMAND WITH ABOVE QUERY:
1 SIMPLE ps fulltext prodname prodname 0 1 Using where; Using temporary; Using filesort

1 SIMPLE p eq_ref PRIMARY,i_products_rating_vis,i_products_added_vis,i_products_sortorder_vis PRIMARY 4 shoppingcart_5521.ps.productid 1 Using where

1 SIMPLE pi ref i_product_images_imageprodid i_product_images_imageprodid 5 shoppingcart_5521.p.productid,const 1

Member Avatar
johny_d
Junior Poster in Training
92 posts since May 2007
Reputation Points: 23 [?]
Q&As Helped to Solve: 7 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,
Have you found a solution for this yet?
If not, then... 1. can you put the three tables in a zip and attach them here so I can replicate them locally and try some changes?
and 2. 'gold' in this query is just an example for any dynamic input keyowrd, like a search string, right?

Member Avatar
rch1231
Veteran Poster
1,187 posts since Sep 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 194 [?]
Skill Endorsements: 13 [?]
 
0
 

Hello,

Quick suggestions are: Create a view instead of a query and make sure you have indexes on the fields you are linking the tables with.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: