SEO

#1 (**permalink**) 02-28-2008, 08:50 AM

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` ) ;

Take care,
Quarco

#2 (**permalink**) 02-28-2008, 08:50 AM

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` ) ;
>
>
> Take care,
> Quarco

The former will create 3 indexes, allowing you fast access (and or sorting)
to data based on any of the 3 fields.
The latter will only build one index and the speed improvement will
(broadly) be limited to queries based on fieldA.

#3 (**permalink**) 02-28-2008, 08:50 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Diffrence in 'add index' question