[QUESTION] USING INDEX

[Anon]

Test Database : MySQL

create table test1(
id int not null default '0',
s_id int not null default '0',
g_id int not null default '0',
primary key(id),
key classID (s_id,g_id)
);
mysql> explain select * from test1 where s_id =1;

Don't used index

but

mysql> explain select * from test1 where s_id =1 and g_id =1;

Used index

also

create table test2(
id int not null default '0',
s_id int not null default '0',
g_id int not null default '0',
primary key (id),
key classID (s_id)
);
mysql > explain select * from test2 where s_id =1;

Used index

create table test3{
id int not null default '0',
s_id char(10) not null,
g_id char(10) not null,
primary key (id),
key classID (s_id,g_id)
);

mysql> explain select * from test3 where s_id='abc';

Used index

I don't understand. Why don't use index of test1 ?

Please help me!!! Anyone explain the appearance.

[Anon]

I wonder why use index in test3... In test1 it's simple: key is a pair <s_id,g_id>. If you ask about whole index, index is in use. If you ask about part of index whole index can't be use. Difference between test1 and test3 my raise from different way of constructing indexes based on int and char variables. In case of char it could be simple concatenation and 'part' of index could be used. In case of int it could be use same math transformation which can't be divided into its part. Sorry, but I don't know what transformation it could be... This answer is based on my experience with Informix and Watcom (now Sybase).