Re: Optimizing a query so that the smaller table is handled first

[Jørgen Løland]

Almost correct :)

There are two issues:
1) efficient access to the rows that hold a certain property, and
2) performing the join in the most efficient order

Issue 1 is about appropriate indices that can quickly give you rows with a certain color/size/etc as requested by the query. If there is no index on the color column, the entire table has to be read to find rows with "red". Depending on the size of the table and the cardinality of the property, scanning may be much more expensive than using an index.

Issue 2 is about starting with the table that will produce fewest rows. Even if there is no index in the color column of Dim, the MySQL optimizer may still decide that scanning the dimension table is likely to produce fewer rows than accessing the appropriate index in the fact table. However, no index means no statistics, so MySQL has to make a guess guess. IIRC, MySQL will guess that one 10th of all rows will match the condition, but don't take my word for it...

My suggestion: add indices on all dimension columns that will be used as conditions in the queries.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com