MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT version();


version()


8.0.22


CREATE TABLE forum 
(
  id INTEGER PRIMARY KEY,
  mainthreadid INTEGER,
  section INTEGER,
  approved INTEGER,
  my_date INTEGER (20),
  title VARCHAR (255),
  my_text VARCHAR (255),
  INDEX my_d_ix (my_date),
  INDEX mtd_ix (mainthreadid)
);

INSERT INTO forum (id, mainthreadid, section, approved, my_date, title, my_text)
VALUES 
(1, 1, 5, 1, 1000, "title1", "text1"),
(2, 2, 5, 1, 1000, "title2", "text2"),
(3, 2, 5, 1, 1001, "title3", "text3"),
(4, 2, 5, 1, 1002, "title4", "text4"),
(5, 2, 5, 1, 1003, "title5", "text5"),
(6, 2, 5, 1, 1004, "title6", "text6"),
(7, 2, 5, 0, 1005, "title7", "text7"),
(8, 8, 150, 1, 1004, "title8", "text8"),
(9, 1, 5, 1, 1006, "title9", "text9"),
(10, 1, 5, 1, 1005, "title10", "text10");

SELECT * FROM forum;


id
mainthreadid
section
approved
my_date
title
my_text


1
1
5
1
1000
title1
text1

2
2
5
1
1000
title2
text2

3
2
5
1
1001
title3
text3

4
2
5
1
1002
title4
text4

5
2
5
1
1003
title5
text5

6
2
5
1
1004
title6
text6

7
2
5
0
1005
title7
text7

8
8
150
1
1004
title8
text8

9
1
5
1
1006
title9
text9

10
1
5
1
1005
title10
text10


--
-- Eliminate approved = 0 and section = 150
-- We can see that we want the max of date and mainthreadid 
-- as per Akina's solution. However, we will get them in a different way.
--
-- We can see that records with rn = 1 are the desired resultset!
--
-- Unfortunately, we can't use Window functions in a WHERE clause
--
SELECT
  ROW_NUMBER() OVER (PARTITION BY mainthreadid ORDER BY my_date DESC) AS rn,
  my_date,
  id,
  mainthreadid
FROM forum
WHERE approved = 1
AND section != 150
ORDER BY mainthreadid, my_date DESC


rn
my_date
id
mainthreadid


1
1006
9
1

2
1005
10
1

3
1000
1
1

1
1004
6
2

2
1003
5
2

3
1002
4
2

4
1001
3
2

5
1000
2
2


SELECT my_date, id, mainthreadid, rn -- rn not strictly necessary
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY mainthreadid ORDER BY my_date DESC) AS rn,
    my_date,
    id,
    mainthreadid
  FROM forum
  WHERE approved = 1
  AND section != 150
) AS tab
WHERE rn = 1
ORDER BY my_date DESC, mainthreadid;


my_date
id
mainthreadid
rn


1006
9
1
1

1004
6
2
1


EXPLAIN ANALYZE 
SELECT my_date, id, mainthreadid, rn -- rn not strictly necessary
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY mainthreadid ORDER BY my_date DESC) AS rn,
    my_date,
    id,
    mainthreadid
  FROM forum
  WHERE approved = 1
  AND section != 150
) AS tab
WHERE rn = 1
ORDER BY my_date DESC, mainthreadid;


EXPLAIN


-> Sort: tab.my_date DESC, tab.mainthreadid  (actual time=0.007..0.007 rows=2 loops=1)
    -> Index lookup on tab using <auto_key0> (rn=1)  (actual time=0.001..0.002 rows=2 loops=1)
        -> Materialize  (actual time=0.059..0.060 rows=2 loops=1)
            -> Window aggregate: row_number() OVER (PARTITION BY forum.mainthreadid ORDER BY forum.my_date desc )   (actual time=0.034..0.040 rows=8 loops=1)
                -> Sort: forum.mainthreadid, forum.my_date DESC  (cost=1.25 rows=10) (actual time=0.032..0.034 rows=8 loops=1)
                    -> Filter: ((forum.approved = 1) and (forum.section <> 150))  (cost=1.25 rows=10) (actual time=0.017..0.022 rows=8 loops=1)
                        -> Table scan on forum  (cost=1.25 rows=10) (actual time=0.015..0.019 rows=10 loops=1)



SELECT my_date, id, mainthreadid
FROM forum
JOIN ( SELECT mainthreadid, MAX(my_date) my_date
       FROM forum
       WHERE approved = 1 AND section != 150
       GROUP BY mainthreadid ) lastdates USING (mainthreadid, my_date)
ORDER BY my_date DESC;


my_date
id
mainthreadid


1006
9
1

1004
6
2


EXPLAIN ANALYZE
SELECT my_date, id, mainthreadid
FROM forum
JOIN ( SELECT mainthreadid, MAX(my_date) my_date
       FROM forum
       WHERE approved = 1 AND section != 150
       GROUP BY mainthreadid ) lastdates USING (mainthreadid, my_date)
ORDER BY my_date DESC;


EXPLAIN


-> Sort: forum.my_date DESC  (actual time=0.150..0.151 rows=2 loops=1)
    -> Stream results  (actual time=0.127..0.141 rows=2 loops=1)
        -> Nested loop inner join  (actual time=0.126..0.139 rows=2 loops=1)
            -> Filter: (lastdates.my_date is not null)  (actual time=0.087..0.089 rows=2 loops=1)
                -> Table scan on lastdates  (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=1)
                    -> Materialize  (actual time=0.087..0.088 rows=2 loops=1)
                        -> Group aggregate: max(forum.my_date)  (actual time=0.073..0.079 rows=2 loops=1)
                            -> Filter: ((forum.approved = 1) and (forum.section <> 150))  (cost=1.25 rows=1) (actual time=0.031..0.038 rows=8 loops=1)
                                -> Index scan on forum using mtd_ix  (cost=1.25 rows=10) (actual time=0.029..0.034 rows=10 loops=1)
            -> Filter: (forum.mainthreadid = lastdates.mainthreadid)  (cost=0.38 rows=0) (actual time=0.022..0.024 rows=1 loops=2)
                -> Index lookup on forum using my_d_ix (my_date=lastdates.my_date)  (cost=0.38 rows=1) (actual time=0.022..0.023 rows=2 loops=2)
id	mainthreadid	section	approved	my_date	title	my_text
1	1	5	1	1000	title1	text1
2	2	5	1	1000	title2	text2
3	2	5	1	1001	title3	text3
4	2	5	1	1002	title4	text4
5	2	5	1	1003	title5	text5
6	2	5	1	1004	title6	text6
7	2	5	0	1005	title7	text7
8	8	150	1	1004	title8	text8
9	1	5	1	1006	title9	text9
10	1	5	1	1005	title10	text10