I am making a dynamic query with this code:
$query = $this->entityTypeManager->getStorage('node')->getQuery();
$query->accessCheck(FALSE);
$query->condition('status', 1);
$query->condition('type', $node_bundle);
$query->condition($taxonomy_field_name, $node_term_id);
$query->condition('nid', $node->id(), '<>');
$orGroup = $query->orConditionGroup();
foreach($recurrences as $recurrence){
$date_start = $recurrence['value'];
$date_end = $recurrence['end_value'];
$andGroup = $query->andConditionGroup();
$andGroup->condition($smart_date_field_name . '.end_value', $date_start, '>');
$andGroup->condition($smart_date_field_name . '.value', $date_end, '<');
$orGroup->condition($andGroup);
}
$query->condition($orGroup);
$nodes_id = $query->execute();
Depending of the number of $recurrences the query has more or less joins.
Using dpm($query->__toString()), the query that I obtain is:
SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
INNER JOIN node__field_reserva_sala_sala node__field_reserva_sala_sala ON node__field_reserva_sala_sala.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora ON node__field_reserva_sala_data_i_hora.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_2 ON node__field_reserva_sala_data_i_hora_2.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_3 ON node__field_reserva_sala_data_i_hora_3.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_4 ON node__field_reserva_sala_data_i_hora_4.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_5 ON node__field_reserva_sala_data_i_hora_5.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_6 ON node__field_reserva_sala_data_i_hora_6.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_7 ON node__field_reserva_sala_data_i_hora_7.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_8 ON node__field_reserva_sala_data_i_hora_8.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_9 ON node__field_reserva_sala_data_i_hora_9.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_10 ON node__field_reserva_sala_data_i_hora_10.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_11 ON node__field_reserva_sala_data_i_hora_11.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_12 ON node__field_reserva_sala_data_i_hora_12.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_13 ON node__field_reserva_sala_data_i_hora_13.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_14 ON node__field_reserva_sala_data_i_hora_14.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_15 ON node__field_reserva_sala_data_i_hora_15.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_16 ON node__field_reserva_sala_data_i_hora_16.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_17 ON node__field_reserva_sala_data_i_hora_17.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_18 ON node__field_reserva_sala_data_i_hora_18.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_19 ON node__field_reserva_sala_data_i_hora_19.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_20 ON node__field_reserva_sala_data_i_hora_20.entity_id = base_table.nid
LEFT JOIN node__field_reserva_sala_data_i_hora node__field_reserva_sala_data_i_hora_21 ON node__field_reserva_sala_data_i_hora_21.entity_id = base_table.nid
WHERE (node_field_data.status = '1') AND (node_field_data.type = 'reserva_sala') AND (node__field_reserva_sala_sala.field_reserva_sala_sala_target_id = '17') AND (node_field_data.nid <> '77') AND (((node__field_reserva_sala_data_i_hora.field_reserva_sala_data_i_hora_end_value > '1681284600') and (node__field_reserva_sala_data_i_hora.field_reserva_sala_data_i_hora_value < '1681290000')) or ((node__field_reserva_sala_data_i_hora_2.field_reserva_sala_data_i_hora_end_value > '1681889400') and (node__field_reserva_sala_data_i_hora_2.field_reserva_sala_data_i_hora_value < '1681894800')) or ((node__field_reserva_sala_data_i_hora_3.field_reserva_sala_data_i_hora_end_value > '1682494200') and (node__field_reserva_sala_data_i_hora_3.field_reserva_sala_data_i_hora_value < '1682499600')) or ((node__field_reserva_sala_data_i_hora_4.field_reserva_sala_data_i_hora_end_value > '1683099000') and (node__field_reserva_sala_data_i_hora_4.field_reserva_sala_data_i_hora_value < '1683104400')) or ((node__field_reserva_sala_data_i_hora_5.field_reserva_sala_data_i_hora_end_value > '1683703800') and (node__field_reserva_sala_data_i_hora_5.field_reserva_sala_data_i_hora_value < '1683709200')) or ((node__field_reserva_sala_data_i_hora_6.field_reserva_sala_data_i_hora_end_value > '1684308600') and (node__field_reserva_sala_data_i_hora_6.field_reserva_sala_data_i_hora_value < '1684314000')) or ((node__field_reserva_sala_data_i_hora_7.field_reserva_sala_data_i_hora_end_value > '1684913400') and (node__field_reserva_sala_data_i_hora_7.field_reserva_sala_data_i_hora_value < '1684918800')) or ((node__field_reserva_sala_data_i_hora_8.field_reserva_sala_data_i_hora_end_value > '1685518200') and (node__field_reserva_sala_data_i_hora_8.field_reserva_sala_data_i_hora_value < '1685523600')) or ((node__field_reserva_sala_data_i_hora_9.field_reserva_sala_data_i_hora_end_value > '1686123000') and (node__field_reserva_sala_data_i_hora_9.field_reserva_sala_data_i_hora_value < '1686128400')) or ((node__field_reserva_sala_data_i_hora_10.field_reserva_sala_data_i_hora_end_value > '1686727800') and (node__field_reserva_sala_data_i_hora_10.field_reserva_sala_data_i_hora_value < '1686733200')) or ((node__field_reserva_sala_data_i_hora_11.field_reserva_sala_data_i_hora_end_value > '1687332600') and (node__field_reserva_sala_data_i_hora_11.field_reserva_sala_data_i_hora_value < '1687338000')) or ((node__field_reserva_sala_data_i_hora_12.field_reserva_sala_data_i_hora_end_value > '1687937400') and (node__field_reserva_sala_data_i_hora_12.field_reserva_sala_data_i_hora_value < '1687942800')) or ((node__field_reserva_sala_data_i_hora_13.field_reserva_sala_data_i_hora_end_value > '1688542200') and (node__field_reserva_sala_data_i_hora_13.field_reserva_sala_data_i_hora_value < '1688547600')) or ((node__field_reserva_sala_data_i_hora_14.field_reserva_sala_data_i_hora_end_value > '1689147000') and (node__field_reserva_sala_data_i_hora_14.field_reserva_sala_data_i_hora_value < '1689152400')) or ((node__field_reserva_sala_data_i_hora_15.field_reserva_sala_data_i_hora_end_value > '1689751800') and (node__field_reserva_sala_data_i_hora_15.field_reserva_sala_data_i_hora_value < '1689757200')) or ((node__field_reserva_sala_data_i_hora_16.field_reserva_sala_data_i_hora_end_value > '1690356600') and (node__field_reserva_sala_data_i_hora_16.field_reserva_sala_data_i_hora_value < '1690362000')) or ((node__field_reserva_sala_data_i_hora_17.field_reserva_sala_data_i_hora_end_value > '1690961400') and (node__field_reserva_sala_data_i_hora_17.field_reserva_sala_data_i_hora_value < '1690966800')) or ((node__field_reserva_sala_data_i_hora_18.field_reserva_sala_data_i_hora_end_value > '1691566200') and (node__field_reserva_sala_data_i_hora_18.field_reserva_sala_data_i_hora_value < '1691571600')) or ((node__field_reserva_sala_data_i_hora_19.field_reserva_sala_data_i_hora_end_value > '1692171000') and (node__field_reserva_sala_data_i_hora_19.field_reserva_sala_data_i_hora_value < '1692176400')) or ((node__field_reserva_sala_data_i_hora_20.field_reserva_sala_data_i_hora_end_value > '1692775800') and (node__field_reserva_sala_data_i_hora_20.field_reserva_sala_data_i_hora_value < '1692781200')) or ((node__field_reserva_sala_data_i_hora_21.field_reserva_sala_data_i_hora_end_value > '1693380600') and (node__field_reserva_sala_data_i_hora_21.field_reserva_sala_data_i_hora_value < '1693386000')))
When I execute that query with $query->execute() the database use all CPU and is not able to respond.
If I execute the same query with less joins it works fine and it retrieves values.
In addition, if I execute the same query on mysql client directly also I have the same problem.
Why it happens? Where could be the problem?
[Specs of my server]
OS: Debian 11
Web: Apache/2.4.56
PHP: 8.1.17
DB version: 10.5.18-MariaDB-0+deb11u1 (also I have tried with MySQL Server)
DB Engine: InnoDB
DB size: 34 MB
Drupal: 10.0.7