How to index lookup table in MySQL

Question

I have a table of 10M rows productwith fields like color (int), price (float), weight (float), unitprice (int),etc. Now users from the Web dynamically generate search data requests from this table with random conditions (the color should be here), for example,

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 filter fields (with range, sort ...) like this in MySQL?

---

EDIT: In my opinion, MySQL will most likely select only one index for the query, and only the left side of the composite index will work. Obviously, indexing all possible combinations is not a possible option, for example (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 to quickly search / filter / sort in any field, you should put indexes on all of them.

If color must be used in each query (i.e. used in each query), it is best to create composite indexes on (color, field)for each field.

Including a clustered index on top (color, product_id)can also be useful if colorit is truly part of every common query.

Answer 2

Tomalak, , , ( , ). , , .

, , explain.