How to index a lookup table in MySQL

Question

I have a 10M-row table product with fields like color (int), price (float), weight (float), unitprice (int), etc ... Now users from Web dynamically generate queries to lookup data from this table with random conditions (color is a must have here) and order-by such as

select * from product where color=1 and price >5 and price <220 and .... order by unitprice limit 75, 25;

select count(*) from product where color=3 and weight <500 and price <30 ... ;

How to index a table (InnoDB or NDB) with about 10 possible filtering fields (with range, sorting ...) like this in MySQL?

---

EDIT: In my understanding MySQL most likely will pick only one index for a query, and only the left hand part of a composite index will work. Obviously indexing all possible combinations is not a feasible option, such as (color, price, weight, create_date, unitprice, ....), (color, weight, price, create_date, unitprice, ....), (color, unitprice, weight, ....) .... Not all conditions are necessarily present in all queries.

What would you do to index this table?

Answer 1

If you want quick lookups/filters/sorts on any field, you must put indexes on all of them.

If color ist a must have (i.e. used in every query), it's best to make composite indexes on (color, field) for each field you have.

Putting the clustered index over (color, product_id) might also be worth a try if color really is a part of every common query.

Answer 2

As Tomalak already answered, you probably should add indexes for all fields (and composite indexes, depending on your queries). But of course this can slow writes down.

If you're not sure how the indexes will be used, you can use the explain command.