Why my mysql DISTINCT doesn't work?

Why the two query below return duplicate member_id and not the third?

I need the second query to work with distinct. Anytime i run a GROUP BY, this query is incredibly slow and the resultset doesn't return the same value as distinct (the value is wrong).

SELECT member_id, id 
FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u 
LIMIT 5

+-----------+--------+
| member_id | id     |
+-----------+--------+
|     11333 | 313095 |
|    141831 | 313094 |
|    141831 | 313093 |
|     12013 | 313092 |
|     60821 | 313091 |
+-----------+--开发者_运维百科------+

SELECT distinct member_id, id 
FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u 
LIMIT 5

+-----------+--------+
| member_id | id     |
+-----------+--------+
|     11333 | 313095 |
|    141831 | 313094 |
|    141831 | 313093 |
|     12013 | 313092 |
|     60821 | 313091 |
+-----------+--------+

  SELECT distinct member_id
    FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u 
    LIMIT 5

+-----------+
| member_id |
+-----------+
|     11333 |
|    141831 |
|     12013 |
|     60821 |
|     64980 |
+-----------+

my table sample

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `s_type_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `s_FI_1` (`member_id`),
  KEY `s_FI_2` (`s_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=313096 DEFAULT CHARSET=utf8;

DISTINCT is a keyword you can only apply on the whole SELECT, and not on a single field. It ensures the database doesn't return two identical rows. This is why your second query with DISTINCT returns only one time each member_id while your first returns it twice. In its result set, each row is indeed unique, even though you can get several times the same member_id.

it works, its dirty (no index, no key, temporary table...) but it works,

SELECT member_id,id 
FROM ( SELECT member_id,id, created_at FROM table1 ORDER BY created_at desc ) as u 
group by member_id ORDER BY created_at desc LIMIT 5;

In first query, there is no distinct keyword. In 2nd query it is selecting distinct rows. not distinct member id. In the third query, there is only member id, so it is selecting the distinct member ids

SELECT distinct member_id, id FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u LIMIT 5

member_id is not unique. So that makes the query show more rows...

Create the following indexes:

CREATE INDEX ix_table1_createdat ON table1 (created_at);
CREATE INDEX ix_table1_memberid_createdat ON table1 (member_id, created_at);

and use this query:

SELECT  t1i.*
FROM    (
        SELECT  DISTINCT member_id
        FROM    table1 tdi
        ORDER BY
                created_at DESC
        LIMIT 5
        ) t1d
JOIN    table1 t1i
ON      t1i.id =
        (
        SELECT  t1o.id
        FROM    table1 t1o
        WHERE   t1o.member_id = t1d.member_id
        ORDER BY
                t1o.member_id DESC, t1o.created_at DESC
        LIMIT 1
        )