Speeding up queries

Hello,

I have a simple web store developed in php which has 400k+ product rows in the database.

I have a simple mySQL function which grabs optimal top products for each category:

$order_by = "clicks, views"
$limit = 10;

$sql = SQL>>
(SELECT * FROM products WHERE site_id = $site_id AND category_id = $category_id AND price > 3000 ORDER BY $order_by LIMIT $limit)
UNION
(SELECT * FROM products WHERE site_id = $site_id AND category_id > 0 AND price > 4000 ORDER BY $order_by LIMIT $limit)
UNION
(SELECT * FROM products WHERE site_id = $site_id AND category_id > 0 ORDER BY $order_by LIMIT $limit)
UNION
(SELECT * FROM products WHERE site_id = $site_id AND price > 4000 ORDER BY $order_by LIMIT $limit)
UNION
(SELECT * FROM products WHERE site_id = $site_id ORDER BY $order_by LIMIT $limit)

The problem is that this sql query will take up to 9 seconds to execute in the current database (with 400k+ products).

How can I speed it up beside setting indexes?

Should I only set index on site_id and category_id ?
Which all indexes should I set to get optimal speed?

What else can I do beside caching sql query, and reducing the Union’s?

Thanks for help.

hmm it depends how spread out your data is e.g. if you only have 2 distinct site_id then an index on that won’t be that effective.
my first guess is adding an index on (site_id, category_id)
and (clicks,view)

after adding the indices, could you paste an ‘explain’ plan for the full SQL, as well as an explain plan for the individual selects within the union?
prefix the select with ‘explain’ and paste the results
e.g.
explain SELECT * FROM products WHERE site_id = 1 AND category_id = 2 AND price > 3000 ORDER BY clicks, views LIMIT 10

The purpose of the query is to display most relevant top products for the desired category.

Some products have category_id set and some don’t, so the mysql statement is executed with backup queries (to get minimum 10 products):

  1. show products from category with the price above x, get first 10 ordered by popularity (views and clicks)

  2. find products from other categories, within the same price range and order

  3. find products from other categories, without price range and same order

  4. find products related to the site, with price range and in popularity order

  5. find products related to the site, without price range in popularity order

This could probably be cut down and also improved. Any ideas what would help the most to still get relevant results in fall-back scenarios?

I set the indexes as you said, and I get below 0.1 lookup time which is quite amazing in comparison to 9 seconds :slight_smile:

Thanks for help

this is more of a marketing question, and I’m not familiar with your domain.
I think you have already got most options based on your existing database.

some questions:
would there be such a thing as ‘related categories’ or ‘related sites’?
do you have stats on volume sold for particular products? maybe that is more effective than views.
What is the difference between clicks and views?