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    |
PRIMARY,id,building_id_index | 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 and
how to fix this performance problem?

---

Clarification of Question by joefinkle-ga on 08 Oct 2005 08:23 PDT

It appears that other queries involving joins between box.building_id
and buildings.id are also using the ALL instead of eq_ref, and
likewise suffer tremendous performance hits. Here are the prints of
both tables:

| box   | CREATE TABLE `box` (
  `box_id` int(10) unsigned NOT NULL auto_increment,
  `building_id` varchar(32) collate latin1_bin NOT NULL default '',
  `type` varchar(64) collate latin1_bin NOT NULL default '',
  `model` int(11) default NULL,
  `speed` smallint(5) unsigned default NULL,
  `phone_number` varchar(64) collate latin1_bin NOT NULL default '',
  `last_call_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
  `last_call_success` tinyint(4) NOT NULL default '0',
  `last_successful_call_timestamp` timestamp NULL default '0000-00-00 00:00:00',
  `enabled` tinyint(1) NOT NULL default '1',
  `call_attempts` tinyint(3) unsigned NOT NULL default '0',
  `remote_answer` varchar(64) collate latin1_bin default NULL,
  `in_use` varchar(128) collate latin1_bin NOT NULL default '0',
  PRIMARY KEY  (`box_id`),
  KEY `building_id_index` (`building_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |


| buildings |CREATE TABLE `buildings` (
  `id` varchar(32) NOT NULL default '',
  `name` varchar(60) default NULL,
  `corporate_name` varchar(60) default NULL,
  `owner_id` varchar(15) default NULL,
  `address` varchar(255) default NULL,
  `energy_type` varchar(16) default NULL,
  `oiltank_capacity` int(10) unsigned default NULL,
  `oil_type` varchar(16) default NULL,
  `oil_provider` varchar(32) default NULL,
  `square_feet` int(10) unsigned default NULL,
  `units` int(10) unsigned default NULL,
  `exposure` varchar(64) default NULL,
  `photo` blob,
  `photo_type` varchar(10) default NULL,
  `boiler_id` varchar(42) default NULL,
  `boiler_type` varchar(60) default NULL,
  `boiler_bday` date default NULL,
  `boiler_cleaning_date` date default NULL,
  `burner_id` varchar(42) default NULL,
  `burner_type` varchar(60) default NULL,
  `burner_bday` date default NULL,
  `burner_last_overhaul` date default NULL,
  `oiltank_cleaning_date` date default NULL,
  `bb_ann_insp_date` date default NULL,
  `bb_triann_insp_date` date default NULL,
  `oiltank_type` varchar(24) default NULL,
  `oiltank_id` varchar(24) default NULL,
  `last_tightness_test` date default NULL,
  `last_pet_store_app` date default NULL,
  `notes` blob,
  `super_name` varchar(45) default NULL,
  `super_apartment` varchar(10) default NULL,
  `super_phone` varchar(16) default NULL,
  `super_pager` varchar(16) default NULL,
  `super_cell` varchar(16) default NULL,
  `date_of_construction` date default NULL,
  `base_price_structure` varchar(24) default NULL,
  `base_markup_2` decimal(7,4) default NULL,
  `base_markup_4` decimal(7,4) default NULL,
  `base_markup_6` decimal(7,4) default NULL,
  `gas_type` varchar(24) default NULL,
  `gas_provider` varchar(36) default NULL,
  `gas_user` varchar(56) default NULL,
  `gas_password` varchar(56) default NULL,
  `block` varchar(10) default NULL,
  `lot` varchar(10) default NULL,
  `borough` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `building_id_index` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |