Question 113472 in Mysql Question posted in
The official MySQL documentation can be found here.

Speed up Queries – Average calculation in Mysql

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search