Optimization problem unless LIMIT is included (any LIMIT)

Hello,

I wonder if this is a bug I found here...

Say I have a `posts` table, and I want to query this `posts` table for the latest Posts by my Friends.

Here's an "obvious" query (my User ID being 12345, and reducing the lookup by RID > 49251041):

SELECT `posts`.`rid`

FROM `posts`

STRAIGHT_JOIN `friends` ON `id1`="12345" AND `id2`=`posts`.`from`

WHERE `posts`.`rid`>"49251041" ORDER BY `posts`.`rid` DESC LIMIT 5000

This is taking 3.6s

Looks "optimized" enough...

Since I found this really strange, I tried an alternative:

SELECT `posts`.`rid`

FROM `posts`

STRAIGHT_JOIN (SELECT id2 FROM `friends` WHERE `id1`="12345") AS `f` ON `f`.`id2`=`posts`.`from`

WHERE `posts`.`rid`>"49251041" ORDER BY `posts`.`rid` DESC LIMIT 5000

This is also taking 3.6s

The EXPLAIN looks pretty much the same, with a few more "rows" on the posts table.

HOWEVER, by putting *any* LIMIT inside the subquery, no matter how big or small it is,

SELECT `posts`.`rid`

FROM `posts`

STRAIGHT_JOIN (SELECT id2 FROM `friends` WHERE `id1`="12345" LIMIT 5000) AS `f` ON `f`.`id2`=`posts`.`from`

WHERE `posts`.`rid`>"49251041" ORDER BY `posts`.`rid` DESC LIMIT 5000

It straight away makes the query instant...
0.0387s

In the EXPLAIN, I see an extra "step" that I'm not sure what it means:

–

Even if I have something like:

LEFT JOIN `friends` ON `id1`="12345" AND `frid`=`posts`.`from`

...

WHERE ... (`friends`.`frid` IS NOT NULL OR (...)) ...

It's still taking its time...
As soon as I remove this "friends" bit, it becomes instant.

–

In terms of amount of rows...

SELECT COUNT(*) FROM `posts` WHERE `rid`>"49251041";

27754

SELECT COUNT(*) FROM `friends` WHERE `uid`="12345";

649

In terms of indexes,

posts:

friends:

Any ideas?

Why do I need to put a LIMIT, and the basic STRAIGHT_JOIN isn't efficient by default?

Note - this is on a much more complex query, but the above is a minimal example where the issue happens.

–

Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1