EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

MySQL: Using Union to split multiple Select queries into respective tables

Database type:

I have the following tables:

Product(maker, model, type)

PC(model, speed, ram, hd, price)

Laptop(model, speed, ram, hd, screen, price)

Printer(model, color, type, price)

I need to write a query that will return the average price of all products made by each maker, but only if that average is >= 200, in descending order by price.

I have tried 2 different methods and both get me very close but not exactly what I need:

(SELECT maker, AVG(price) as a
FROM Product NATURAL JOIN PC
WHERE price >= 200
GROUP BY maker)

UNION

(SELECT maker, AVG(price) as b
FROM Product NATURAL JOIN Laptop
WHERE price >= 200
GROUP BY maker)

UNION

(SELECT maker, AVG(price) as c
FROM Product NATURAL JOIN Printer
WHERE price >= 200
GROUP BY maker)

ORDER BY a;

The above gives me the average prices made by each maker for all the products they have made but it is all in one column so you cannot visually tell what product each average is linked to.

SELECT maker,

(SELECT AVG(price)
FROM PC
WHERE price >= 200) as 'Average Cost of PCs',

(SELECT AVG(price)
FROM Laptop
WHERE price >= 200
GROUP BY maker) as 'Average Cost of Laptops',

(SELECT AVG(price)
FROM Printer
WHERE price >= 200
GROUP BY maker) as 'Average Cost of Printers'

FROM Product
GROUP BY maker;

The above successfully gives each type of product its own column and also a column for all the makers, but it gives the average cost for all PCs, Printers, and Laptops in their respective columns instead of the average cost of each made by the maker it is parallel to.

Im not sure which one I am closer to the answer with but I've hit a wall and I'm not sure what to do. If I could get the first code to divide into different columns it would be correct. if I could get the second one to average correctly it would be right.

I am very new to Stack Overflow so I apologize if I did not ask this question in the correct format

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.

Optimal indexes for this query:

ALTER TABLE `Laptop` ADD INDEX `laptop_idx_price` (`price`);
ALTER TABLE `Laptop` ADD INDEX `laptop_idx_maker` (`maker`);
ALTER TABLE `PC` ADD INDEX `pc_idx_price` (`price`);
ALTER TABLE `Printer` ADD INDEX `printer_idx_price` (`price`);
ALTER TABLE `Printer` ADD INDEX `printer_idx_maker` (`maker`);
ALTER TABLE `Product` ADD INDEX `product_idx_maker` (`maker`);

The optimized query:

SELECT
        Product.maker,
        (SELECT
            AVG(PC.price) 
        FROM
            PC 
        WHERE
            PC.price >= 200) AS 'Average Cost of PCs',
        (SELECT
            AVG(Laptop.price) 
        FROM
            Laptop 
        WHERE
            Laptop.price >= 200 
        GROUP BY
            Laptop.maker 
        ORDER BY
            NULL) AS 'Average Cost of Laptops',
        (SELECT
            AVG(Printer.price) 
        FROM
            Printer 
        WHERE
            Printer.price >= 200 
        GROUP BY
            Printer.maker 
        ORDER BY
            NULL) AS 'Average Cost of Printers' 
    FROM
        Product 
    GROUP BY
        Product.maker 
    ORDER BY
        NULL

Question 2

EverSQL Database Performance Knowledge Base

MySQL: Using Union to split multiple Select queries into respective tables

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles