Performance in Mysql Database Queries

Question

What is the best option in terms of performance? I do

select * from agenda, paciente where agenda.id_paciente = paciente.id_paciente 

and take the data together from the agenda and patient or do first

select * from agenda 

and then another select inside the loop

select * from paciente where id_paciente = agenda.id_paciente

Answer 1

Use two SELECTS separate makes sense only if you are taking data from different tables or very varied criteria to each record.

Every time you make one query by PHP, a new request is made to the DB server, which has to do the planning and execution of the data. Sending only a dry query (like a Join simple), planning is done once and results are processed in a more optimized way by DBMS.

In the given example, it would look like this:

SELECT
   agenda.data,
   paciente.nome
   ... etc, especificando os campos um a um ...
FROM
   agenda
LEFT JOIN
   paciente ON agenda.id_paciente = paciente.id_paciente

Note these points:

• If you want performance, and data saving, you should not use the *, and set the fields you will use, one by one.

  There is no problem using the *, provided you have a real reason to use it, which does not seem to be the case for the example; however, when specifying the fields manually, you have only processing and traffic of the data you really need, and their order is predictable in the return of the data, even if the table will have new fields in the future.

  Also, since you have more than one table in use, by typing the fields you avoid ambiguities and mistakes that can go unnoticed when the two tables have fields of the same name.

• if you specify the type of Join and uses the ON clause, has control over the form that the Join is made.

  The way you did with where works, but there you are depending on the Planner DB understand what you want, and it is not always the best way if the complexity of query true be great.

Behold in this post the essential JOIN types to define the best for the real case.