I have a slow problem when making a query to the database, it's from a friend's website and I don't have much idea of php but the section that makes the query is the following code.
function get($id_usuario=false){
global $wpdb;
if($id_usuario){
$tabla = $wpdb->prefix."mod_usuarios";
$consulta = "Select * from $tabla where id_usuario='$id_usuario' ";
$resultado = $wpdb->get_results($consulta);
foreach($resultado as $fila){
$clCliente = new acClientes();
$clCliente->formateaSalida($fila);
}
return $clCliente;
}
else{
$tabla = $wpdb->prefix."mod_usuarios";
$consulta = "Select * from $tabla ";
$resultados = $wpdb->get_results($consulta);
$array = array();
$i=0;
foreach($resultados as $fila){
$array[$i] = new acClientes();
$array[$i]->formateaSalida($fila);
$i++;
}
return $array;
}
There are about 900 records (and 6 fields) and when I query the database using the Select * from wp_mod_usuarios
it takes less than half a second. But if I do it like this with the code above it takes more than 20 seconds.
I don't know where the problem is, I have been reading that with php it is not the same as doing it through phpmyadmin but so much difference?
Thanks in advance
There are several factors why a code can be slow.
Notice that I said code and not query , because sometimes the problem may not be the query.
However, your SQL statement is not optimized .
SELECT *
unless you are going to use all the fields or columns of the table . Let's imagine a table with 20, 40, 50, 100 columns of which in a given query you will only need theid, nombre, apellidos ...
, if you do aSELECT *
, which I unfortunately see very often... if your table has 50 columns and you only need 3, you would be unnecessarily selecting 47 columns with which you are not going to operate at that moment. So: 1st optimization rule, no, no and not aSELECT *
EXPLAIN SELECT columna1, column3, solocolumnaquenecesites FROM tabla WHERE ...
At first it seems complicated, but throughEXPLAIN
you'll quickly see how the table's indexes are working. It would be worth spending a little time on. In this question and its answers you will be able to understand the operation ofEXPLAIN
clearly. It is an excellent ally to optimize our tables. Here when I talk about indicesI am referring not only to primary and foreign keys, but also to unique indices. For example, when an index is wrongEXPLAIN
, it will tell you that instead of reading 1, 2, 5... rows, your query is reading 900 or 10,000 or several million rows, especially when you join several tables throughJOIN
your SQL statement. (Not the case here, but I think it's worth pointing out.)The other part of the code that has nothing to do with the query to the DB. It may be that the optimization problem is on the side of the code that handles the data already obtained, in this case the functions:
Or also here:
It turns out that PDO has methods to return to us in an array (or in many other ways) our result set. And it is another error that I frequently see in OS code, I mean I see code that reinvents the wheel , trying to do, sometimes with little success, something that the database management driver already does through their own methods.
P.S:
If you decide on PDO , here you have a ready-made class (although I have yet to improve it) that would serve you for the basic functions of querying and handling data safely.
I don't know where the problem could be, but I added a couple of improvements:
Regarding your yield problem, I would bet on point 3 or something related to your declaration of the global.
Greetings.