mysql - Multiple or single Compound index -

let's have table id,a,b,c,d,e,f,g around 1 million rows. 1 make query multiple where ...and...and...etc conditions in multiple combinations. example a , b , e or a , f , g or e , f , g.

so account combinations, have create multiple compound indexes if a,b,c,d,e,f,g have range [1,10] therefore no zero.

could 1 make single compound per start variable a,b,c,d,e,f,g , b,a,c,d,e,f,g etc.. , during query time like

  #b , e have not been chosen   select * a=3 , b!=0 , c=4 , d=5 , e!=0 , f=1 , g=9   #i think logic 

could such procedure allow mysql still use compound index or need create possible combinations of compound indexes.

the end result reduce number of indexes 7 instead of number of left combinations possibles way higher 7.

mysql use compound indexes in order if can. if data represents taxonomy single index do. let's customers can type either business or personal, , live in given postal code, , status premium or regular, query

select * customer  type = 'business'  , postal_code = '12345'  ,  status = 'premium'; 

would able use index based on compound key built of type + postal_code + status. if didn't know status, index still useful. if only knew postal_code not type, index not used -- order matters.

but agree comment strawberry -- typically not problem in standard relational schema. it's not unusual have several foreign keys in table, unless you're building data cube, or other special design, problem isn't 1 should having -- not 7 fields.

but if real problem, consider value of each potentially indexed field. if queries able narrow million rows down few thousand using several indexes (compound or not) final scan may trivial. experiment explain plan see @ point stops mattering queries.

the cost of maintaining index may trivial ... or not. in highly tuned transactional systems, single insert, update, or delete result in n+1 writes: 1 row, , other n each index. if you're reading, may fine. if not, combination of compound keys potentially have benefit reducing number of writes.

but have been working relational database more few decades. cases scenario arises have been resolved rethinking schema design; don't recall case compound key made more sense multiple indexes in typical relational , well-normalized schema.