List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 11 1999 7:39pm
Subject:Re: Indexing for joins
View as plain text  
At 3:43 AM -0500 9/11/99, Roger Hales wrote:
>Although the manual says an index can be used to "retreive rows from other
>tables when performing joins", I'm not sure of the best way to go about
>For example:  if the following is used
>select * from t1,t2 where t1.col2=t2.col5;
>how should the tables be indexed?
>Roger Hales

A couple of simple rules:

- You want to index the columns that you use in the WHERE clause,
not the ones you're selecting (if they're different)

- The columns you're joining on should be the same type.

Without knowing more, it appears you want to index t1.col2
and t2.col5, at least for the query in question.
You can see what MySQL makes of your query by putting
an EXPLAIN in front of the SELECT.

Paul DuBois, paul@stripped
Indexing for joinsRoger Hales11 Sep
  • Re: Indexing for joinsPaul DuBois11 Sep
    • Re: Indexing for joinsRoger Hales12 Sep