When to create multiple-column index?
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first 2 columns, the first 3 columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

SELECT * FROM test WHERE first_name='Andrew';

SELECT * FROM test
  WHERE last_name='Smith' OR first_name='Andrew';


Suppose that you issue the following SELECT statement:

mysql&gt; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;


If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization..

Source: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html