Query takes ages

[c_pattle]

I have the following query that takes 69 seconds, which I reckon is going to be a tad bit too slow to use on a public facing website  .  I was wondering if anyone knows how I can speed up the query. 

 

SELECT product_data.*, products.supplier_ref, product_pricing.sales_price_inc_vat, products.type_id, MATCH(product_data.product_name) AGAINST ("Metacam") AS score FROM product_data, products, product_pricing WHERE products.type_id = 1 AND product_data.product_id=products.product_id AND product_data.product_id=product_pricing.product_id  AND products.type_id = 1 ORDER BY score DESC LIMIT 20

 

Let me know if you want me to put the table structures on here but they are quite big!

 

Thanks

[fenway]

Start by posting EXPLAIN output -- my guess is that you don't have a full-text index, or that those joins aren't using indexes.

[c_pattle]

Thanks, this was the results

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE product_data ALL NULL NULL NULL NULL 12432 Using temporary; Using filesort

1 SIMPLE product_pricing ALL NULL NULL NULL NULL 5558 Using where

1 SIMPLE products eq_ref PRIMARY PRIMARY 4 animed.product_data.product_id 1 Using where

 

So I guess I need to add some indexes?

[fenway]

Yup.

 

A FULLTEXT index on (product_data.product_name).

 

An INDEX on product_data.product_id.

 

An INDEX on product_pricing.product_id.