Query Performance in MySql Database

Posted on

Question :

What is the best performance option? I do

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

and pick up 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 :

Using two%% of separated% makes sense only if you are going to get data from different tables or very different criteria for each record.

Each time you do a query with PHP, a new request is made to the DB server, which has to do the planning and execution of the data. By sending a query only if it is lean (as a simple join ), planning is done once and the results are processed in a more optimized way by the DBMS.

In the example given, it looks like this:

   ... etc, especificando os campos um a um ...
   paciente ON agenda.id_paciente = paciente.id_paciente

Note these points:

  • If you want performance, and data economy, you should not use SELECTS , but rather define the fields you will use, one by one.

    It’s okay to use * , as long as you have a real reason to use it, which does not look like the example; however, by specifying the fields manually, you have only the processing and traffic of the data you really need, and their order is predictable on the data return, even if the table will have new fields in the future.

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

  • If you specify the join type and use the ON clause, you have control over the way that join is done.

    The way you did with * works, but then you’re depending on the planner of DB to understand what you want, and it’s not always the best way if the complexity of query is great.

See in this post the essential JOIN types to define the better for the real case.


Leave a Reply

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