LEFT JOIN in one query or two queries with IN?

Question

MySQL

Good afternoon. Who faced the following problem, which will work faster of the two implementation options?
joomla
class .... extends JTable
1st option.

{
  $query = "SELECT rtc.id, rtc.created, rtc.published, rtc.content_id, rtc.user_id, rtcc.content
                  FROM `#__jshopping_rtcomments` as rtc
                  LEFT JOIN `#__jshopping_rtcomments_content` as rtcc ON rtc.content_id = rtcc.content_id 
                  LIMIT 50";
  $this->_db->setQuery($query);
  $result = $this->_db->loadObjectList();
}

or 2nd option

{
  $query = "SELECT rtc.id, rtc.created, rtc.published, rtc.content_id, rtc.user_id
                  FROM `#__jshopping_rtcomments` as rtc
                  LIMIT 50";
  $this->_db->setQuery($query);
  $result_A = $this->_db->loadObjectList('id');
  
  $query = "SELECT rtcc.content_id, rtcc.content
                  FROM `#__jshopping_rtcomments_content` as rtcc 
                  WHERE rtcc.content_id IN (" . implode(", ", array_keys($result_A)) . ")";
  $this->_db->setQuery($query);
  $result_B = $this->_db->loadObjectList('content_id');
}

In the first case, how does sql work, first left join of two large tables, and then limit? Whether left join will not slow down when joining tables with 300k-400k records. And in the second case, won't php itself slow down?

Answer 1

To find out what will be faster - you need to understand the order in which the request is executed. MySQL in this regard is a rather specific database with a lot of its own features.
The easiest way to find out the query execution speed is to see how long the query is executed in the MySQL administration program (any sane one, for example, HeidiSQL).
A slightly more complicated way is to use EXPLAIN .

Answer 2

Naturally, the variant with one request will work faster. Limit is processed after everything in the request, and does not affect the actual processing of the request.

Answer 3

EXPLAIN SELECT .....
in addition to seeing the speed of both queries, you can also see where the non-indexed search is going and optimize