Speed up Queries - Average calculation in Mysql-CodePudding

Hi how can I optimse my two queries for average and margin calculation?

The table has over 3 million records. On a small data set the following queries work fine but on the big data set the queries block because of time out.

Table script:

CREATE TABLE `Prices` (`country` text,`Date` int,`items` varchar(250),`priceEuro` int,`url` varchar(250))

PriceAVG View calculation:

CREATE VIEW 
PriceAVG AS
Select distinct 
Prices.country, 
Prices.items, 
(SELECT ROUND(AVG(priceEuro)) FROM Prices AS T1 WHERE T1.items = Prices.items AND T1.country = Prices.country) AS average 
FROM Prices;

Margin calculation:

CREATE VIEW 
Margin AS
SELECT  
Prices.country, 
Prices.items, 
Prices.priceEuro, 
PriceAVG.average, 
(PriceAVG.average - Prices.priceEuro) AS margin,
Prices.url  
FROM Prices
INNER JOIN PriceAVG ON Prices.items = PriceAVG.items AND Prices.country = PriceAVG.country  
WHERE EXISTS (SELECT 1 FROM PriceAVG WHERE Prices.items = PriceAVG.items AND PriceAVG.country = 'Canada' )  
ORDER BY `margin` DESC

CodePudding user response：

Simplified version of your data might look like

DROP TABLE IF EXISTS T;

CREATE TABLE T(COUNTRY INT, PRICE INT);
INSERT INTO T VALUES
(1,10),(1,20),(2,30);

The avg can be calculated in a subquery and joined to main query like this

SELECT T.COUNTRY,PRICE,AVGPRICE, PRICE-AVGPRICE MARGIN
FROM T
JOIN
(
SELECT COUNTRY , AVG(PRICE) AVGPRICE
FROM T 
GROUP BY COUNTRY
) A ON A.COUNTRY = T.COUNTRY;

 --------- ------- ---------- --------- 
| COUNTRY | PRICE | AVGPRICE | MARGIN  |
 --------- ------- ---------- --------- 
|       1 |    10 |  15.0000 | -5.0000 |
|       1 |    20 |  15.0000 |  5.0000 |
|       2 |    30 |  30.0000 |  0.0000 |
 --------- ------- ---------- --------- 
3 rows in set (0.001 sec)

If this is too simple for you consider adding representative sample data as text to the question.

CodePudding user response：

Not sure why you are using a sub-select

Select p.country
     , p.items
     , ROUND(AVG(p.priceEuro)) AS average 
FROM Prices p
GROUP BY p.country
       , p.items;

Should calculate the average per country/items. For the margin it is probably easiest to use a window function:

SELECT p.country
     , p.items
     , p.priceEuro 
     , p.average, 
     , p.average - p.priceEuro AS margin
     , p.url  
FROM (
  SELECT country
     , items
     , priceEuro 
     , AVG(priceEuro) OVER (PARTITION BY country, items) AS average
     , url
  FROM Prices
) AS p