1 index for 2 kinds of queries below possible in MYSQL?

Question

select xx from tablexx where type in (1,3) and last<current-interval 30 second;

select xx from tablexx where type=1;

If create index on (type,last),the first one won't use index.

If create index on (last,type),the second one won't use index.

As for data type,which is can be seen from the example,type: int unsigned,last: datetime

Answer 1

The second query, having only type = 1 in the where clause, only needs and index on type, not on (type, last).

MySQL should pick the most specific index for your query, so creating an index just covering type should be used for the second one, but not the first one.

Answer 2

You stated "If create index on (type,last),the first one won't use index." Are you sure about this? I was under the impression this is exactly the circumstance under which a covering index would execute. EDIT: Unless of course there's a selectivity problem with the data - if most records have type 1 or 3 then the optimizer wouldn't use the index (regardless of whether it was a basic or composite index).