indexing - Getting MySQL to use multiple indices per table -

i remember reading somewhere mysql can use 1 index per table select query, don't know if still true recent versions of mysql. couldn't find topic.

i'm trying optimize query on table has multiple indices, explain shows it's using 1 of indices. tried using force index(index1,index2), isn't working. there way force mysql use multiple indices in table?

i'm using mysql 5.6.15.

basically mysql can use 1 index while retrieving rows table.

there special cases when mysql able scan multiple indexes single table.

called index merge optimalization , details describes under link:
http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

few basic examples can find in demo

there couple of indexes on mytable table in demo:

create index m_x on mytable( x ); create index m_y on mytable( y ); 

and there 3 queries (you need click on view execution plan links in demo see expains):

intersection access algorithm:

select * mytable x = 4    , y = 7 ; possible_keys = m_x,m_y key           = m_x,m_y         = using intersect(m_x,m_y); using 

union access algorithm:

select * mytable x = 5     or y = 3 ; possible_keys = m_x,m_y key           = m_x,m_y         = using union(m_x,m_y); using 

sort union access algorithm:

select * mytable  x > 49 or y < 1  ; possible_keys = m_x,m_y key           = m_x,m_y         = using sort_union(m_x,m_y); using 

if there few indexes created on table, mysql tries apply these optimizations automatically, don't need special turn on them except creating indexes.
mysql uses these optimalizations in special , rare cases.
in other cases combined index (single index on multiple columns) can used