Arias Gonzalez, Javier wrote:
Assuming we have the following table:
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
);
With last_name having 1,000 different values and first_name having
1000,000 different values... What is better (if any) at the time of
querying the database:
A) To define an index like: "INDEX name (last_name,first_name)" and
perform a query like: "select * from test where last_name='aaa' and
first_name='bbb'"
B) To define an index like: "INDEX name (first_name,last_name)" and
perform a query like: "select * from test where first_name='bbb' and
last_name='aaa'"
I'd go for this one, so the first_name cuts down a lot of what you're
searching for. Make sure your index matches (see next comment).
C) It is irrelevant the order of the index definition.
The order of the indexed fields is extremely relevant.
Mysql (and other db's) will traverse things left to right. So in your
example, if you create an index on:
first_name, last_name
but your queries are:
last_name='x', first_name='y'
mysql will not be able to use that index because it doesn't match up.
To take it further, if you have this:
create table a(
a int,
b int,
c int
);
create index blah on a(a,b,c);
and your query is:
select * from a where a='x' and b='y' and c='z';
Mysql will be able to use an index across all 3 columns.
If your query is:
select * from a where a='x' and c='z' and b='y';
then mysql will only be able to use the index to find the right 'a'
values, and it will have to scan the results for the right b and c values.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
