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

[Jeff Goldberg]

I've made a little progress on this.
I figured out that I can force the join order using the MySQL keyword "STRAIGHT_JOIN", such as:

>
SELECT STRAIGHT_JOIN SUM(d.units_sold) TOTAL_UNITS_SOLD
FROM date_dimension dd, daily_sales_fact d
WHERE dd.dt = '2011-04-02' AND d.sales_date = dd.date_dimension_id;
>


The EXPLAIN for this is:
>
id, select_type, table, type, possible_keys, key, ken_len, ref, rows, Extra
1, SIMPLE, dd, ALL, PRIMARY, , , , 4194, Using where
1, SIMPLE, d, ref, PRIMARY,fk_daily_sales_sales_date1, fk_daily_sales_sales_date1, 4, dg.dd.date_dimension_id, 2304,
>

This performs much better than the original.

I can also achieve this improvement by using a LEFT JOIN
>
SELECT SUM(d.units_sold) TOTAL_UNITS_SOLD
FROM date_dimension dd
LEFT JOIN daily_sales_fact d ON d.sales_date = dd.date_dimension_id
WHERE dd.dt = '2011-04-02'
>

Which has the same effect of forcing the date_dimension to go first.
In this case LEFT JOIN gives us identical results, but that might not always be true and I want to understand how to optimize for this in the general case.

Plus, neither fix is as good as the version where I do two queries and manually plug in the id in the second query.