Q wrote:

> Suppose table:
>
>
> CREATE TABLE `testtable` (
> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
> `fieldA` INT NOT NULL ,
> `fieldB` INT NOT NULL ,
> `fieldC` INT NOT NULL ,
> `fieldD` INT NOT NULL
> ) ENGINE = MYISAM ;
>
> What's the diffrence between:
> ALTER TABLE `testtable` ADD INDEX ( `fieldA` )
> ALTER TABLE `testtable` ADD INDEX ( `fieldB` )
> ALTER TABLE `testtable` ADD INDEX ( `fieldC` )
>
> ...And:
> ALTER TABLE `testtable` ADD INDEX ( `fieldA` , `fieldB` , `fieldC` ) ;


The first will create 3 separate indexes, one for each of fieldA, B and C.
This will vastly speed up queries like SELECT * FROM testtable WHERE fieldB
= 1;

The second will create one index on all three fields. The will vastly speed
up queries that query fieldA, B and C at the same time.

For example:

SELECT * FROM testtable WHERE fieldA = 1 AND fieldB = 9 AND fieldC
= 'Hello';

Mysql will only use 1 index per table per query so in the first case it
would use which ever index has the most unique records. Lets assume fieldA
does.

It would use the fieldA index to find just the rows where fieldA = 1. But
after that it must compare all of those rows against fieldB = 9 AND fieldC
= 'Hello' to get the result. Consider a table with millions of rows, this
would not be fast.

However, if you have all three fields indexed together, it will be able to
find the matching rows from the index alone - much faster.


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png