SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
-
Dec 1, 2008, 20:31 #1
- Join Date
- Dec 2008
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Problem getting most recent record
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.
-
Dec 2, 2008, 09:33 #2
- Join Date
- Dec 2008
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code MySQL: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:
Code MySQL:
Joining this query to other tables won't be a problem as long as you use the right indexes in the left table.
Bookmarks