How to combine four SQL queries into one?

Question

PHP

Good evening.
How to connect four SQL queries together?

// $type = '1';
// $model = 'ABARTH';
// $model = '500';
// $capacity = '1400';

    function getECUTypes($type, $brand, $model, $capacity) {

      $dbc = self::getConnection();

      $query = "SELECT `auto_sel_brand_id` FROM `auto_sel_brand` WHERE `auto_sel_brand_name` = '$brand' AND `auto_sel_brand_type` = '$type'";

      $result = $dbc->query($query) or DIE($dbc->error);

      $rows = $result->fetch_assoc();

      $brand_id = $rows['auto_sel_brand_id'];

      $query = "SELECT `auto_sel_model_id` FROM `auto_sel_model` WHERE `auto_sel_model_name` = '$model' AND `auto_sel_model_brand` = '$brand_id'";

      $result = $dbc->query($query) or DIE($dbc->error);

      $rows = $result->fetch_assoc();

      $model_id = $rows['auto_sel_model_id'];

      $query = "SELECT `auto_sel_ecu_id` FROM `auto_sel_motor` WHERE `auto_sel_ecu_motor` = '$model_id' AND `auto_sel_ecu_name` = '$capacity'";

      $result = $dbc->query($query) or DIE($dbc->error);

      $rows = $result->fetch_assoc();

      $ecu_id = $rows['auto_sel_ecu_id'];

      $query = "SELECT `auto_sel_motor_name` FROM `auto_sel_ecu` WHERE `auto_sel_motor_model` = '$ecu_id'";

      $result = $dbc->query($query) or DIE($dbc->error);

      $result_array = [];
      while ($rows = $result->fetch_assoc()) {
          array_push($result_array, $rows);
      }

      return $result_array;

    }

Each SQL query returns some value needed for the next query, specifically a unique key in the form of an ID. How to optimize? This code is terrible and slow.

Answer 1

1) read about join
2) read about indexes here , here and here .
3) compose a request and run it through explain.
4) Set up indexes...
5) Profit

Answer 2

Didn't check how it is

$sub_query = "SELECT t3.`auto_sel_ecu_id` FROM `auto_sel_brand` t1
JOIN `auto_sel_model` t2 ON t2.`auto_sel_model_brand` = t1.auto_sel_brand_id AND t2.`auto_sel_model_name` = '$model'
JOIN `auto_sel_motor` t3 t3.`auto_sel_model_brand` = t2.'auto_sel_brand_id' AND AND `auto_sel_ecu_name` = '$capacity'
WHERE t1.`auto_sel_brand_name` = '$brand' AND t1.`auto_sel_brand_type` = '$type'";

$query = "SELECT `auto_sel_motor_name` FROM `auto_sel_ecu` WHERE `auto_sel_motor_model` IN ('$sub_query')";

$result = $dbc->query($query) or DIE($dbc->error);

....

Answer 3

Gluing through joins will not help, you have flat queries and should work instantly, profile queries individually and see what and how.
As they advise above and below - that's what it turns out to be, there will be no optimization! since you get the result in the form of an ID purely based on the results of the next request, then the whole canoe will stand and wait - it's better if this canoe waits in the application (PHP) than the whole canoe hangs in MySQL memory waiting ...
Set on SC , where the level of specialists is significantly higher, ideally if Mike answers ...
UPD. By the way, why do you use fetch_assoc to get one ID?????))))