SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    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.

  2. #2
    SitePoint Member
    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:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •