Slow query MySQL

Question

Slow query MySQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

If you could give me a hand. I am trying to perform a query to get a count of records that are in the activated table that belong to the point of sale 2 and at the same time the total of their records in number, but it takes up to 45 seconds in answer for the amount of information. Is there a more efficient way to do it? Thanks

SELECT cr.nombre, (SELECT COUNT(n.id)
    FROM numero n
    INNER JOIN carrier c ON c.id = n.carrier_id
    INNER JOIN cliente cl ON cl.id = n.cliente_id
    INNER JOIN clave_cliente cc ON cc.cliente_id = cl.id
    WHERE c.id = cr.id
    AND cc.puntoVenta_id = 2
    GROUP BY cr.nombre) AS total, 
    (SELECT COUNT(a.id)
    FROM numero n
    INNER JOIN activado a ON a.numero_id = n.id
    INNER JOIN carrier c ON c.id = n.carrier_id
    INNER JOIN cliente cl ON cl.id = n.cliente_id
    INNER JOIN clave_cliente cc ON cc.cliente_id = cl.id
    WHERE c.id = cr.id
    AND cc.puntoVenta_id = 2
    GROUP BY cr.nombre) AS activados
FROM carrier cr
WHERE cr.activo = TRUE;

mysql sql

asked by Javier SL 01.11.2018 в 18:13

source

2 answers

0

The first problem is the construction of the query, from what I can tell you that without doubt the use of subqueries as columns and as part of group by many times an important bottleneck. From what I can understand of your code, what you are looking for you could solve it like this:

SELECT  c.nombre,
        COUNT(n.id) AS total
    FROM carrier c
    INNER JOIN numero n ON c.id = n.carrier_id
    INNER JOIN cliente cl ON cl.id = n.cliente_id
    INNER JOIN clave_cliente cc ON cc.cliente_id = cl.id
    WHERE   cc.puntoVenta_id = 2
            and c.activo = TRUE
    GROUP BY c.nombre

For deeper optimizations you should know the indexes and the explain plan.

answered by 01.11.2018 в 21:33

Search and filter URL in a flat file (Bash) [closed] Error with materialize in Aurelia project

score 0 · Accepted Answer

Unfortunately I'm already dating, but your problem is that you put the subqueries in the SELECT, which is executed for each record in the table carrier whose field activo is TRUE.

That is, if you have 10 active records, the subquery that gives you the total field and the subquery that gives you the activated field are executed 10 times. If there are 100, 100 are executed and so on.

Try to use derived queries / tables, that is, use your subqueries as if they were Tables and do a JOIN with the Carrier table. Something like this:

SELECT 
  cr.nombre, 
  t.total as total, 
  a.total as activados 
FROM carrier cr 
  inner join 
    -- primera subconsulta
   (SELECT ci.id, COUNT(n.id) as total 
    FROM numero n 
      INNER JOIN carrier c ON c.id = n.carrier_id 
      INNER JOIN cliente cl ON cl.id = n.cliente_id 
      INNER JOIN clave_cliente cc ON cc.cliente_id = cl.id 
    WHERE 
      --c.id = cr. AND 
      cc.puntoVenta_id = 2 
    GROUP BY 
      cr.nombre
  ) AS t on t.id = cr.id 
  inner join 
    -- segunda subconsulta
   (SELECT 
      c.id, 
      COUNT(a.id) as total 
    FROM 
      numero n 
      INNER JOIN activado a ON a.numero_id = n.id 
      INNER JOIN carrier c ON c.id = n.carrier_id 
      INNER JOIN cliente cl ON cl.id = n.cliente_id 
      INNER JOIN clave_cliente cc ON cc.cliente_id = cl.id 
    WHERE 
        --c.id = cr.id AND  
        cc.puntoVenta_id = 2 
    GROUP BY 
      cr.nombre
  ) AS a on a.id = cr.id 
WHERE 
  cr.activo = TRUE;

To both subqueries I have returned the carrier.id apart from its total. The first time I gave the alias t and the second the alias a .

I do not have time to try it but I hope it serves you.