Hi,

I have 2 table using one-to-many relationship and trying to get the last record in one table based on the id from the first table but can't seem to get that row without ordering which makes use of filesort and I don't want that because the table will grow quite fast.

Her are my tables:

Code MySQL:
| assets | CREATE TABLE `assets` (
  `asset_id` smallint(5) unsigned NOT NULL auto_increment,
  `client_id` smallint(5) unsigned NOT NULL,
  `model_id` smallint(5) unsigned NOT NULL,
  `device_id` smallint(5) unsigned default NULL,
  `serial_no` varchar(50) default NULL,
  `asset_name` varchar(50) default NULL,
  `asset_description` varchar(255) default NULL,
  `color` varchar(20) NOT NULL,
  `date_added` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `date_removed` timestamp NULL default NULL,
  `archived` bit(1) NOT NULL default '\0',
  `active` bit(1) NOT NULL default '',
  PRIMARY KEY  (`asset_id`),
  KEY `client_id` (`client_id`),
  KEY `device_id` (`device_id`),
  KEY `asset_name` (`asset_name`(20)),
  KEY `archived` (`archived`),
  KEY `active` (`active`),
  KEY `model_id` (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

Code MySQL:
| logs_info_streams | CREATE TABLE `logs_info_streams` (
  `log_id` int(10) unsigned NOT NULL auto_increment,
  `device_id` smallint(5) unsigned NOT NULL,
  `server_time` timestamp NOT NULL default '0000-00-00 00:00:00',
  `device_time` timestamp NOT NULL default '0000-00-00 00:00:00',
  `info_type` varchar(10) NOT NULL,
  `info_msg` varchar(255) default NULL,
  `device_txt_date` varchar(10) default NULL,
  `device_txt_time` varchar(8) default NULL,
  PRIMARY KEY  (`log_id`),
  KEY `server_time` (`server_time`),
  KEY `device_time` (`device_time`),
  KEY `device_id` (`device_id`),
  KEY `info_type` (`info_type`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 |

So I'm basically trying to find the most recent row before given device_time in logs_info_streams by passing the asset_id from assets and joining using device_id. Also I need to select by info_type as well.

I tried several query options but all use the filesort, here's one of them:

Code MySQL:
mysql> explain select l.* FROM assets a JOIN logs_info_streams l ON (a.asset_id = 1 and a.device_id = l.device_id) WHERE l.info_type = 'IGR' and l.device_time < 20081128130000 order by l.device_time DESC limit 1;
+----+-------------+-------+-------+---------------------------------+-----------+---------+-------+------+----------------+
| id | select_type | table | type  | possible_keys                   | key       | key_len | ref   | rows | Extra          |
+----+-------------+-------+-------+---------------------------------+-----------+---------+-------+------+----------------+
|  1 | SIMPLE      | a     | const | PRIMARY,device_id               | PRIMARY   | 2       | const |    1 | Using filesort |
|  1 | SIMPLE      | l     | ref   | device_time,device_id,info_type | device_id | 2       | const |    7 | Using where    |
+----+-------------+-------+-------+---------------------------------+-----------+---------+-------+------+----------------+
2 rows in set (0.00 sec)

Any ideas on how to improve this?

Thanks a lot.