When relating two tables, is the correct side of SQL, PHP or whatever?

Question

When relating two tables, is the correct side of SQL, PHP or whatever?

Navigation

#1 by (4 votes)

3

I have this doubt of what it is preferable to do when I need data from the two tables to get the result.

I always did the comparison in PHP, but I learned the SQL relationship commands that could help. What is the most correct way to do it, pertaining to runtime, pattern, and performance?

I used to do this:

 <?php
          $consultaRecomendados=$con->prepare("SELECT * FROM  hf_recomendados");
          $consultaRecomendados->execute();
          $resultadoRecomendados=$consultaRecomendados->fetchAll();
          foreach($resultadoRecomendados as $produtoRecomendado){
               $contultaProdutoRecomendado=$con->prepare("SELECT * FROM  hf_produtos WHERE id=:id");
               $contultaProdutoRecomendado->execute(array(":id"=>$produtoRecomendado['idProduto']));
               $resultadoProdutoRecomendado=$contultaProdutoRecomendado->fetch();
                echo $resultadoProdutoRecomendado['nome']; 
          }
          ?>

And I started doing this:

  <?php
              $contultaRecomendados=$con->prepare("SELECT * FROM  hf_recomendados r JOIN  hf_produtos p
ON r.idProduto=p.id");
              $contultaRecomendados->execute();
              $resultadoRecomendados=$contultaRecomendados->fetchAll();
              foreach($resultadoRecomendados as $produtoRecomendado){
                  echo $produtoRecomendado['nome'];
              }
      ?>

Is there any better or more correct way? What technical considerations should I have?

php sql desempenho pdo

asked by anonymous 17.12.2016 / 21:44

1 answer

Enumeration class in Repository Pattern JavaScript that displays how many months weeks days hours minutes minutes have in x hours

score 4 · Accepted Answer

In a nutshell, it is best to use the DBMS to merge queries.

Every time you run a query on a database it will basically do the following:

Interpret the SQL command to know if the syntax is correct and whether all objects are available to execute. Then convert the SQL to a relational algebra structure.

Perform an analysis to make a query execution plan to separate the parts and define the order of execution. This involves deciding which indexes are available and which algorithms are most efficient for joining the tables, based on the internal statistics that it collects from the bank as the amount of records, indexes, etc.

Execute each part of the algorithm and merge each result to then make the result available to the caller, where he can "fetch" the data.

So when you put JOIN within the SQL query you are telling the database to resolve these joins to you using the best possible mechanism and it will use the code that is internally optimized to do this.

If you choose to do the work he would do internally, your code is likely to be less efficient. There are several ways to do JOINs between tables and this is one of the biggest problem points for a relational database to solve. Even the way you write your queries, if you use JOIN or Subquery , use an IN operator or an EXISTS in> internally affects the execution time because it changes the way the database executes the query.

If you want to delve deeper into the subject, search for "Relational Algebra" and "Query Optimization" and you will find a lot of interesting material.