No luck Dan. The reason why I had the straight join was because the
optimiser was joining the tables in a non-optimal way - it wasn't
using both parts of the key (the same problem I'm having now) so I
changed it to straight join to see if the optimiser would use both
parts of the key.
mysql> exp
Hi again Taras -
Clearly, I was off the mark - sorry!
What if you remove the straight_join specification and let MySQL's JOIN
optimizer do its own thing?
Dan
Taras D wrote:
Sorry everyone, forgot to put in my setup detail!
SQL version: 5.0.22-community-nt
Windows 2000 Professional 5.0, SP
Sorry everyone, forgot to put in my setup detail!
SQL version: 5.0.22-community-nt
Windows 2000 Professional 5.0, SP 4
Dan, I tried your suggestion, but no luck :(.
explain select straight_join * from l,d where (l.sId = d.dId) and
(d.sId in (1,2)) \G
*** 1. row *
Taras, I'm guessing you're using version 4.1 or earlier. This is a
known issue with MySQL prior to 5. 4.1 and earlier would only use one
index per instance of a table per query, leading to poor performance
with OR statements in many cases. In other words, it uses an index to
optimize one of
Hi everyone,
I have the following schema:
create table l
(
aId int unsigned not null auto_increment primary key,
sId smallint unsigned not null,
dId smallint unsigned,
index(sId,dId)
);
create table d
(
sId smallint unsigned not null,
dId smallint unsigned not null,
primary key(sId, dId)
