Hello,
I reached a point I can't go any further with forcing mysql to use the
index as much as needed. I am facing a multi column index of which only
the first column is used.
Let me explain...
This works fine:
create table A ( a mediumint not null, b date not null, index (a,b));
Populate the table with, say 5000 records.
select count(*)
from A
where a in (1,2,3)
and b in ('2004-05-01', '2004-05-02');
The explain command will tell you that de index will be used with
key_len of 6 (3 bytes for mediumint and 3 for date).
Now the next step. Suppose the values of column "a" that we are looking
for can be found in tabel B.
create table B (a mediumint not null primary key);
populate this tabel with 10 rows or so.
Lets do a join, e.g.:
select count(*)
from A, B
where A.a = B.a
and b in ('2004-05-01', '2004-05-02');
And now the index is used with key_len = 3 (i.e. only on column "a")
Until now I found three ways to let mysql use the index with key_len = 6
(i.e. both columns):
1. in cases there is only one date: "and b in ('2004-05-01')"
2. delete rows from A until there are 1500 left or so.
3. avoid the join and pass value a via "where a in (...,...,...)"
The third one is very annoying, since extra queries are needed and
string concatenation in the application is needed. The other 2 are
simply not possible because the resultset becomes irrelevant in that
case ;-)
In reality I use a table with about 7 million rows. And then it really
makes a difference when the index on both columns is used or not!
I used version 4.0.18 and tested with standard setting of mysqd as well
as with these modifications:
key_buffer=64M
table_cache=256
sort_buffer=4M
read_buffer_size=1M
Any suggestion how to deal with this?
Thanks in advance!
Herald
P.S. When needed I can give example dumps to reproduce the problem.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
