0

Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time.

INSERT INTO `test` (`id`, `text`, `time`) 
VALUES
    (1, 'test1', 1),
    (2, 'test1', 3),
    (3, 'test1', 2),
    (4, 'test2', 1),
    (5, 'test2', 100),
    (6, 'test2', 20) ;

The query that meets most of my requirements is:

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    a.`text` ASC
LIMIT 0,50

For small tables, this works perfect. Though in my table (300k+ rows) it makes mysql crash, due to the subquery.

Is it possible to optimize this query? If it cannot be optimized, would it be possible to select the last inserted unique values for text? (the id and time are theoretically uncorrelated, though in 99% of the cases a correlation will be found, whereas the higher the id, the higher the time)

  • 1
    Please post the SHOW CREATE TABLE test; output and the execution plan. Do you have an index on (text,time)? – ypercubeᵀᴹ Jun 4 '13 at 22:30
  • No, there is no index on text, nor time. Only a primary key on id. What index would you suggest to use? – Jeffrey Jun 4 '13 at 23:10
  • Adding indexes turned the time down from more than a minute, to 3/100 of a second. Thank you very much! I'll have to read up about indexes I guess. – Jeffrey Jun 4 '13 at 23:38
1

The obvious index for this query is on (test,time). Add it with:

ALTER TABLE test
  ADD INDEX text_time_IX          -- choose a name for the index
    (test,time) ;

You may also get efficiency gain if you use this version of the query (pushing the limit inside the derived table):

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`
    ORDER BY
        `text` ASC
    LIMIT 0,50 ) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    `text` ASC ;

If there cases where the same maximum time appears on more than one row with same text, it will not show the same exact results and will return more than 50 rows (but I think this version is more appropriate). If you really want the arbitrary choice in case of ties, you can add the LIMIT in the external select, too.

  • Thank you, correct me if i'm wrong, but the outer "order by" can be removed? The extra results (>50) can be "removed" by using a second group by in the outer query (I figured out that this makes the query about 90% slower, but is still 80% quicker than only one group by in the outer query). Considering all options for "order by" and "group by" in the inner and outer query, I found that placing them both in the inner query is quickest. – Jeffrey Jun 5 '13 at 20:23
  • You could remove the outer "order by", if you don't need it. It doesn't change the number of rows returned (not until you add a LIMIT clause). – ypercubeᵀᴹ Jun 5 '13 at 20:25
  • But I don't see how adding another GROUP BY in the outer query would be useful. – ypercubeᵀᴹ Jun 5 '13 at 20:31
  • Adding a second GROUP BY text, in the outer query (after the ON conditions, removes the double 'text' values from the results. Is it correct to say that the first ORDER BY gives the results sorted to the outer query, and thus the second ORDER BY does nothing (because it sorts the came column)? – Jeffrey Jun 5 '13 at 20:38
  • No, a GROUP BY there would be wrong. It would remove double text values but you wouldn't be able to use SELECT a.* then (or rather, you would be able to but with indeterminate results.) – ypercubeᵀᴹ Jun 5 '13 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.