Mysql: How to make index on a table

I would like to know when i have to put multiple index should i make them under the same "index" or on a different index?

IN开发者_如何学运维DEX `field1` (`field1`)
INDEX `field2` (`field2`)

or

INDEX `field1` (`field1`,`field2`)

Are there any differneces?

thanks

There is a difference. An index made out of two columns (or more) is built according to the order of columns you specified. It can be used only when you are searching for the first X fields. Let's say you have an index

exampleIndex (`a`, `b`, `c`)

It will be used when running queries like:

SELECT * FROM tbl WHERE a = 1;
SELECT * FROM tbl WHERE a = 1 AND b = 2;
SELECT * FROM tbl WHERE a = 1 AND b = 2 AND c = 3;

It will NOT be used when running queries like:

SELECT * FROM tbl WHERE b = 2;
SELECT * FROM tbl WHERE c = 3;
SELECT * FROM tbl WHERE b = 2 AND c = 3;

On queries of the first type, having the 3 field index will be quicker than having separate indexes, so deciding how to build the indexes depends on which types of queries you are going to need.

Yes, there is a difference. Look: when you gonna run query like

SELECT * WHERE a = '1' and b = '2'

you will create one index for columns a and b:

INDEX `field1` (`field1`,`field2`)

Two indexes will be slower.

Sorry for my bad english.