MySQL poor join performance ?

[R Lenard]

Hi,

I've been seeing *REALLY* poor performance when joining a small table and a large table (to filter the large table) - is this really expected ?

I have 1 large table (which has lots of data in it) and want to reduce the data returned by filtering based on criteria in the small table.

e.g. SELECT a.value from large a, criteria b where a.src = b.id and ((b.src = 'A' and b.dest = 'B') or (b.src = 'B' and b.dest = 'C')) and a.dir = 'src->dest';

Table 'a' has ~5 million rows
Table 'b' has < 100 rows.

the (a.src, a.dir) is the primary key on the large table
b.id is the primary key in the criteria table

Explain QUERY shows that the join is not using the index - que ??

If I rewrite the query to remove the join (e.g. SELECT value from large where (a.constraint = 'idForSrcAAndDestB' or a.constraint = 'idForSrcBAndDestC') and a.dir = 'src->dest' then the query is around an order of magnitude quicker (25seconds > 4seconds). But this is evil - having to do a 2 step query ..

Any ideas ?

Thx,
Rohan