CODEWITHSUNDEEP
mysqlsql
Giorgio Torassa
Giorgio Torassa

Reputation: 75

Speed up SQL nested query

I have a table called ntr_perf with the following column: data, cos, network, tot_reg, tot_rej. I need to get the sums of tot_reg and tot_rej for each pair data-cos (I need to take all data-cos pairs and make the sum of the values for all the networks with the same data-cos pair).

I'm using the following MySQL query:

SELECT DISTINCT 
    data AS d, 
    cos AS c, 
    (SELECT SUM(tot_reg) FROM ntr_perf WHERE data=d AND cos=c) AS sumattempts, 
    (SELECT SUM(tot_rej) FROM ntr_perf WHERE data=d AND cos=c) AS sumrej FROM ntr_perf

It takes a very long time even if the table has only 91.450 rows (the table has a multi-column index data-cos).

Is it possible to speed up the query?

Upvotes: 1

Views: 73

Answers (4)

Tedo G.
Tedo G.

Reputation: 1565

this will group your query and filter your sums, as you posted WHERE conditions:

SELECT 
    data AS d, 
    cos AS c,
    SUM(IIF(data='d' AND cos='c', tot_reg, 0) AS sumattempts,
    SUM(IIF(data='d' AND cos='c', tot_rej, 0)) AS sumrej 
FROM 
    ntr_perf 
GROUP BY 
    data,
    cos

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Try this, a group by

SELECT data d,
       cos c,
       SUM(tot_reg) sumattempts,
       SUM(tot_rej) sumrej
FROM ntr_perf 
WHERE data = 'd'  -- if these are values, put in single quotes
AND cos = 'c' -- if these are values, put in single quotes
GROUP BY data, -- even though aliased, the original name needs to be used on the GROUP BY
         cos -- even though aliased, the original name needs to be used on the GROUP BY

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141


You can use this query,

SELECT data AS d, cos AS c,
SUM(tot_reg), SUM(tot_reg) where 
data='d' AND cos='c' group by data , cos ;

Hope you got is. Else let me know, will help you

Upvotes: 0

Emil Holub
Emil Holub

Reputation: 168

This is exactly what group by is designed for.

Try this:

SELECT data,cos,SUM(tot_reg),SUM(tot_rej) from ntr_perf group by data,cos

Upvotes: 1

Related Questions