How to make static nested SELECT faster

Question

Hey

I have a query similar to this

FROM products AS p, ..
LEFT JOIN ( SELECT .. ) AS popularity on popularity.products_id = p.products_id
LEFT JOIN ( SELECT .. ) AS reviews on reviews.products_id = p.products_id
WHERE..
AND..
..

The nested SELECTs from the LEFT JOIN are static, I mean the returned result is not influenced by external values. Those two SELECTs use data from within the db and calculate a result. The results of these queries change rarely, when some user makes a review for a product for example.

The problem is that these SELECTs take a lot of time to complete, they go through each row of their respective tables.

Is there a way I could make this query faster by transforming it into several smaller queries? Or caching the nested selects in some way?

Answer 1

You may want to add some indices to the tables which are used in your subselect statements, with the right indexing a database should be able to return resultsets from millions of rows pretty much instantly.

Answer 2

I agree it's hard to answer without more information. However, check out this post: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql. I've found it quite helpful in the past.

If you look at the very last paragraph, you'll see a technique for saving results of the nested selects in a temporary table.