id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tb ALL PRIMARY,tb_ndx3,tb_ndx4,tb_ndx5 NULL NULL NULL 24238 Using where
1 SIMPLE tb2 ALL PRIMARY,tb_ndx1,tb_ndx2,tb_ndx3 NULL NULL NULL 24063 Using where
1 SIMPLE c eq_ref PRIMARY,c_ndx1 PRIMARY 4 Table1.key 1 NULL
Not sure exactly what this means but the fact that it isn't using keys seems to me that it would be a problem.
Could it be the JOIN condition, which in this case is (tb.a = tb2.a OR (tb.b = tb2.b AND tb.c = tb2.c))
Seems like this should run rather smoothly but I've never done JOIN conditions like this before.
----------
At 05:33 PM 2/25/2004, Daniel Clark wrote:
I know Oracle likes the indexes separatly, but mySQL might like combinations.
> No, we tried individual indexes and then one big grouped index but not > individual indexes on each of the fields. Adding the index actually > added a few seconds to the query so we weren't sure if that was the way > to go. > > I'll try this, though. > > Eric > > At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: >>Do you have separate indexes on: >> >> Table1.ID >> Table2.ID >> Table1.Field1 >> Table2.Field1 >> Table1.Field1 >> Table1.Field2 >> >> > Select * >> > FROM Table1 as a >> > INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = >> b.Field1 and >> > a.Field2 = b.Field2)) >> > WHERE bla bla bla >> > >> > We have tried to set up indexes and the query still takes 8 minutes >> to run. It only returns 6,000 records. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
