MySQL Forums
Forum List  »  Optimizer & Parser

Many Table Scanned instead of One table.
Posted by: Don Thompson
Date: February 15, 2008 10:36AM

I have a many to one table arrangement.

A query like this:

select oneTable.col1 from oneTable, manyTable where manyTable.fk_onTable=OneTable.id and oneTable.col2='something';

Shows in the Explain that the manyTable is scanned first, then the oneTable.

So instead of finding the oneTable rows that match the where condition and then using the index to match the oneTable.id and the manyTable.fk_oneTable (both are indexed), it appears to do it backwards.

Of course, the manyTable has millions of rows and the oneTable typcally has a few thousands and the where clause will restrict the oneTable to about 20-50 rows.

I have noticed several posts discussing similar things, but not many responses.

Options: ReplyQuote


Subject
Views
Written By
Posted
Many Table Scanned instead of One table.
3353
February 15, 2008 10:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.