Problem getting most recent record

[bigolo]

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:

| 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 |

| 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:

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.

[bigolo]

Looks like nobody can help, but anyway after spending a lot of time reading and debugging I found the best optimized query. Here it is:

mysql> 
EXPLAIN SELECT l.* FROM assets a
JOIN logs_info_streams l USE INDEX (device_time)
ON (a.asset_id = 1 AND a.device_id = l.device_id)
WHERE info_type IN ('IGR', 'IGF') AND device_time < 20081128131500
ORDER BY device_time DESC;
+----+-------------+-------+-------+-------------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys     | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY,device_id | PRIMARY     | 2       | const |    1 |             |
|  1 | SIMPLE      | l     | range | device_time       | device_time | 4       | NULL  |    7 | Using where |
+----+-------------+-------+-------+-------------------+-------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

So the problem was that mysql was selecting a different index for the JOIN query instead of using the same index as in ORDER BY.

To summarize this to a very common situations where you have a table with thousands of logs for example and at some you need to get the most recent log event to a date, you can use the following query in order to avoid FILESORT problem:

SELECT * FROM logs_table USE INDEX(log_time) 
WHERE log_time < 20081128131500 AND device_id = 1 AND log_type = 'CHANGE'
ORDER BY log_time DESC LIMIT 1;

Joining this query to other tables won't be a problem as long as you use the right indexes in the left table.