Fetch latest row for a specific identifier

Question

I have a table that looks like this

ID | identifier | data  | created_at
------------------------------------
1  |     500    | test1 | 2011-08-30 15:27:29
2  |     501    | test1 | 2011-08-30 15:27:29
3  |     500    | test2 | 2011-08-30 15:28:29
4  |     865    | test3 | 2011-08-30 15:29:29
5  |     501    | test2 | 2011-08-30 15:31:29
6  |     500    | test3 | 2011-08-30 15:31:29

What I need is the most up to date entry for each identifier, that could be decided by either the ID or the date in created_at. I assumed ID is the better choice due to the indexing.

I would expect this result set:

4  |     865    | test3 | 2011-08-30 15:29:29
5  |     501    | test2 | 2011-08-30 15:31:29
6  |     500    | test3 | 2011-08-30 15:31:29

The result should be ordered by either date or ID in ascending order.

It's important that this is a table that contains ~ 8 millions of rows.

I tried quite some approaches now with self joining and sub queries. Unfortunately all of those came out with either wrong results or half a decade of run time.

To provide an example:

SELECT lo1.* 
FROM table lo1
INNER JOIN
(
    SELECT MAX(id) MaxID, identifier, id
    FROM table 
    GROUP BY identifier
) lo2
  ON lo1.identifier= lo2.identifier
  AND lo1.id = lo2.MaxID
ORDER BY lo1.id DESC
LIMIT 10

The above query takes very long and does sometimes not return the latest result for an identifier, not quite sure why though.

Does anyone have an approach that is able to fetch the required result sets and preferably does not take a decade?

As asked, here is the create code:

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identifier` int(11) NOT NULL,
  `data` varchar(200) COLLATE latin1_bin NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `identifier` (`identifier`),
  KEY `created_at` (`created_at`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Answer 1

The correct query that gives the correct results but it won't scale on larger tables.

Query

SELECT
  `table`.*
FROM
  `table`
INNER JOIN
(
    SELECT
        MAX(id) AS MaxID
      , identifier
    FROM
      `table` 
    GROUP BY
      identifier
    #disables GROUP BY Sorting might make the query faster.
    ORDER BY
      NULL  
) `table_group`
ON
 `table`.ID = `table_group`.MaxID
ORDER BY
  `table`.ID DESC
LIMIT 10

Result

| id | identifier |  data |           created_at |
|----|------------|-------|----------------------|
|  6 |        500 | test3 | 2011-08-30T15:31:29Z |
|  5 |        501 | test2 | 2011-08-30T15:31:29Z |
|  4 |        865 | test3 | 2011-08-30T15:29:29Z |

see demo http://www.sqlfiddle.com/#!9/7f4401/4

But when you check "View Execution Plan" you can see "Using where; Using temporary; Using filesort" in the extra column meaning MySQL needs to use a quicksort algorithm "Using temporary;" means the quicksort algorithm first will be run on a memory temporary table.
If the memory temporary table becomes to large it will be converted to a MyISAM on disk temporary table.
Meaning the quicksort will need disk based random i/o to sort which is slow on disks.
So this method will not scale on the table with ~8 millions of rows.

This query below also gives the same results but it should be more optimized

Query

SELECT 
 `table`.*
FROM
 `table` 
INNER JOIN ( 

  SELECT
    `table`.ID
  FROM
    `table`
  INNER JOIN
  (
      SELECT
          MAX(id) AS MaxID
        , identifier
      FROM
        `table` 
      GROUP BY
        identifier
      #disables GROUP BY Sorting might make the query faster.
      ORDER BY
        NULL  
  )
   AS `table_group`
  ON
   `table`.ID = `table_group`.MaxID
)
  AS `table_group_max`
ON
 `table`.ID = `table_group_max`.ID
ORDER BY
 `table`.ID DESC
LIMIT 10

Result

| id | identifier |  data |           created_at |
|----|------------|-------|----------------------|
|  6 |        500 | test3 | 2011-08-30T15:31:29Z |
|  5 |        501 | test2 | 2011-08-30T15:31:29Z |
|  4 |        865 | test3 | 2011-08-30T15:29:29Z |

see demo http://www.sqlfiddle.com/#!9/7f4401/21

When you check "View Execution Plan" there is no more "Using temporary; Using filesort" meaning the query should be more optimal then the previous query and should in theory execute faster.
Because the combination "Using temporary; Using filesort" can really be a performance killer like explained.