What is more efficient, a complex query or treat the results?

Posted on

Question :

I can do this:

    $sql = 'SELECT * FROM '
                    . 'data_companies '
                    . 'INNER JOIN data_companies_extra_infos ON' 
                    . 'data_companies.cod = data_companies_extra_infos.relationship';
   $rs = MySQLMySQLSelector::select_by_query_string($sql);

And have the result that I want.

Or, I can do this:

  $sql = 'SELECT * FROM '
                . 'data_companies ';
  $data = MySQLMySQLSelector::select_by_query_string($sql);

  $sql_ex = 'SELECT * FROM '
                . 'data_companies_extra_infos ';
  $rs_ex = MySQLMySQLSelector::select_by_query_string($sql_ex);
  $data = [];
  foreach($rs as $row){
      foreach($rs_ex as $row_ex){
           if($row['cod'] == $row_ex['relationship']){
               $data[] = array_merge($row,$row_ex);

And also have the result that I want.
Well, this is a simple example, I believe the first option is better, but in cases where there will be a complex query involving calculations, 4 or 5 tables and multiple filters for WHERE .


Which of the two is better in terms of performance?

In other words:


Is it better for me to do a giant and complex query, or to make multiple simple querys and treat the results with PHP?

PS: The select_by_query_string () function executes the query, treats and encodes the results, and then returns an array.

PS2: I personally find the second option easier.


Answer :

I think the answer will vary according to the scenario.

The sgbd’s before doing a query, try to optimize it by creating a query execution plan.

I see that you are using mysql. One way for you to test the performance of your queries is to use the explain tool of Mysql workbench . It is available from the query menu or by the shortcut Ctlr +
  Alt + X

Through this feature you can see the execution plan that sgbd used and the cost of each step.

So you can define the best way to use your query, need to create indexes, etc. according to the scenario.


Leave a Reply

Your email address will not be published. Required fields are marked *