I'm trying to get the following query optimized:
SELECT box.box_id, box.phone_number, buildings.name as building_name,
buildings.id as building_id, accounts.name as account_name from
boiler_on_off_date LEFT join box ON
box.box_id=boiler_on_off_date.box_id LEFT join buildings FORCE INDEX
(id) ON buildings.id = box.building_id LEFT join accounts ON
accounts.id = buildings.owner_id WHERE boiler_on_off_date.timestamp >
'2005-10-05' GROUP by box.box_id;
Explain Shows the following:
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------------+--------+-----------------------------------------------------------+
| 1 | SIMPLE | boiler_on_off_date | index | NULL |
PRIMARY | 12 | NULL | 192053 | Using
where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | box | eq_ref | PRIMARY |
PRIMARY | 4 | usemgmt.boiler_on_off_date.box_id | 1 |
|
| 1 | SIMPLE | buildings | ALL | id | NULL
| NULL | NULL | 945 |
|
| 1 | SIMPLE | accounts | eq_ref | PRIMARY,id | id
| 15 | usemgmt.buildings.owner_id | 1 |
|
+----+-------------+--------------------+--------+---------------+---------+---------+-----------------------------------+--------+-----------------------------------------------------------+
4 rows in set (0.00 sec)
The problem is the buildings JOIN using type=ALL, which is REALLY slow.
What doesn't make sense is that im joinging building using:
LEFT join buildings FORCE INDEX (id) ON buildings.id = box.building_id
and buildings.id is the PRIMARY KEY on buildings. I also used the FORCE
INDEX(id), which doesn't make any difference either.
Any ideas why buildings is being joined USING ALL and not eq_ref?
Thanks,
Jow