MySQL: Get newest entries older than xxx, Performa

问题:

I have a table that looks like so:

ID | objectID | time | ...
-------------------
 1 |        1 | ...
 2 |        1 | ...
 3 |        1 | ...
 4 |        2 | ...
 5 |        2 | ...
 6 |        3 | ...
 7 |        4 | ...

ID is the primary key, objectID is non-unique. I am trying to increase performance on a query to get the most recent entries for all objectIDs, but the entries should not be newer than a certain value. I tried to following two queries, which should both provide the same (and correct results):

SELECT *
FROM (
    SELECT *
    FROM table
    WHERE time <= XXX
    ORDER BY time DESC
)
GROUP BY objectID

AND

SELECT *
FROM table AS t     
INNER JOIN (
    SELECT ID, MAX(time)
    FROM table
    WHERE time <= 1353143351
    GROUP BY objectID
) s
USING (ID)

An EXPLAIN for the first query tells me

id | select_type | table      | type | possible_keys | key     | key_len | ref  | rows   | Extra
------------------------------------------------------------------------------------
1  | PRIMARY     | <derived2> | ALL  | NULL          | NULL    | NULL    | NULL | 145827 | Using temporary; Using filesort
2  | DERIVED     | tbl_test   | ALL  | NULL          | NULL    | NULL    | NULL | 238694 | Using filesort

for the second query it says

id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows   | Extra
------------------------------------------------------------------------------------
1  | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL | 325 
1  | PRIMARY     | t          | eq_ref | PRIMARY,ID    | PRIMARY | 4       | s.ID | 1   
2  | DERIVED     | tbl_test   | index  | NULL          | ID      | 12      | NULL | 238694 | Using where; Using index; Using temporary; Using filesort

(tbl_test is my table for testing)

The second query seems to be (much) faster, but still not extreme fast with a runtime of 0.1 secs at ~200k DB entries. Is there a way to increase performance of the query? Any missing indexes?

Thanks in advance!!

Edit: Explain for eggys query (see query in his post):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    325 
1   PRIMARY lab_case_test   ALL NULL    NULL    NULL    NULL    238694  Using where; Using join buffer
2   DERIVED lab_case_test   index   NULL    ID  12  NULL    238694  Using where; Using index; Using temporary; Using f...

CREATE TABLE `lab_case_test` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `caseID` int(11) NOT NULL,
 `time` int(11) NOT NULL,
 // ....
 PRIMARY KEY (`ID`),
 KEY `ID` (`ID`,`caseID`,`time`),
 KEY `caseID` (`caseID`,`time`)
) ENGINE=MyISAM AUTO_INCREMENT=238695 DEFAULT CHARSET=utf8

回答1:

You want a composite index over (objectID, time):

ALTER TABLE my_table ADD INDEX (objectID, time)

The reason for this is that MySQL can then retrieve the maximum time for each objectID directly from the index tree; it can then also use the same index in joining against the table again to find the groupwise maximum records using something like your second query (but one should join on both objectID and time—I like to use a NATURAL JOIN in cases like this):

SELECT *
FROM   my_table NATURAL JOIN (
  SELECT   objectID, MAX(time) time
  FROM     my_table
  WHERE    time <= 1353143351
  GROUP BY objectID
) t