Select joining X to Y is ten times faster than joining Y to X -- why is this?

I don't know much about MySQL, just what any non-DBA programmer knows: how to get the data in and out.

I had a page on my site which was running very slowly and the query was the bottleneck. It was taking ten seconds to do this:

    SELECT foo, bar, baz, bax
    FROM small_table
    LEFT JOIN
    large_table
    on small_table.id = large_table.thread_id

I didn't have any indexes, so I indexed the id columns. Now it was taking six seconds.

Then I did a bit of reading, tried the ANALYZE and EXPLAIN commands but didn't really understand them, and then just for the experiment, changed the tables around:

    SELECT foo, bar, baz, bax
    FROM large_table <-- first
    LEFT JOIN
    small_table <-- second
    on small_table.id = large_table.thread_id

and now it开发者_开发知识库 took zero-point-six seconds.

[The app is a forum. small_table lists the threads (100 records), and large_table contains posts made on those threads (20,000 records).]

I can, of course, continue to experiment, but some questions:

• What principle have I uncovered?
• What's the best way to proceed, in general, when I have a slow select?
• In the above example, do I even have the same SELECT as I did before? It produces exactly the same results.
• What was I supposed to learn from ANALYZE and EXPLAIN?

I bet the resulting data are different... Except if the columns you're keeping in the select all are in the small table.

left join keeps every row from the first table and add the rows from the second table when a match exists.

In your first case, the first table is the large on, so you have a lot of rows.

In your second case, the first table is the small on, so you have way less rows, thus the query is faster.