baxy77bax:

As others have mentioned ... your table indexes matter. For example, if you have the two tables defined below, each with a million records:

create table t1 (
   val1 varchar(10) primary key,
   val2 varchar(10)
)

create table t2 (
   tmp1 varchar(10) primary key,
   tmp2 varchar(10)
)
[download]

then the statement:

select t1.val2, t2.tmp2
from t1 join t2 on val2=tmp2
[download]

would likely evaluate to a table scan on t1 with a table scan on t2 for each row in t1, because the join isn't on keyed columns. So it would have to do those table scans on t2 to find the appropriate record(s).

Now the very similar SQL statement:

select t1.val2, t2.tmp2
from t1 join t2 on val1=tmp1
[download]

would evaluate to a table scan on t1 operating in parallel with a single table scan of t2 because the database would be able to take advantage of it's knowledge of the order of items in the two tables.

The overall result is that the first statement is about a million times slower than the second one--all due to indexing.

NOTE: I've oversimplified things a bit, and I don't know the details of mySQL (so I've used syntax compatible with MS SQL Server instead). So while the details may/will be a little off, the overall picture should be accurate.

...roboticus