improve mysql join query execution time

[ss.jatinmehta]

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

[johny_d]

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?

[rch1231]

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.