MySQL index: what's the difference between how 2 fields are indexed?

There are 2 fields that I rely on for searching through the table: field1, field2. They are not unique through all the records, neither individually nor combined.

So I'm adding indexes for them:

Approach one:

alter table xx add index (field1, field2);
alter table xx add index (field2);

Approach two:

alter table xx add index (field1);
alter table xx add index (field2);

My question is what are the differences between these 2 approaches if any? For each of the following select queries:

select * from table xx where field1 = ??
select * from table xx where field2 = ??
select * from table xx where field1 = ?? and field2 = ??
select * from table xx where field1 = ?? or field2 = ??

Wh开发者_JAVA百科ich approach is better?

And, which approach is the better one for this query?

select * from table xx where field1 = ?? and field2 = ??

For the queries you've posted approach 1 will be better, since composite index (field1, field2) will be used for queries with field1 = ?? and field2 = ??

As of field1 = ?? or field2 = ?? query - index merge will be tried to apply, and doesn't matter which approach to choose for this kind query.

To summarize: if you have field1 = ?? and field2 = ?? - then composite index is what you have to choose.