[Solved] Can we Optimise this sql query?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Can we Optimise this sql query?

This is the schema:

  Product(maker, model, type)
  PC(code, model, speed, ram, hd, cd, price)

The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price.

Now, the requirement is to find the printer makers also producing PCs with the lowest RAM capacity and the highest processor speed of all PCs having the lowest RAM capacity.

I wrote this query:

WITH outp AS
(
     SELECT 
         P.Maker, MAX(PC.Speed) AS Speed
     FROM 
         PC
     JOIN 
         Product P ON P.model = PC.Model
     WHERE
         P.maker IN (SELECT maker FROM Product WHERE type = 'Printer')
         AND PC.Ram = (SELECT MIN(ram) FROM PC)
     GROUP BY 
         Maker
)
SELECT maker 
FROM outp
WHERE speed = (SELECT MAX(speed) FROM outp)

This is working but I want to optimise this query or find another query which is much less bulkier than this. Any help?

Thanks guys...

How to optimize this SQL query?

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

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 10): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Avoid Subqueries (query line: 16): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  3. Avoid Subqueries (query line: 29): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  4. 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.
  5. 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 `PC` ADD INDEX `pc_idx_ram` (`ram`);
ALTER TABLE `Product` ADD INDEX `product_idx_model_maker` (`model`,`maker`);
ALTER TABLE `Product` ADD INDEX `product_idx_type` (`type`);
The optimized query:
WITH outp AS (SELECT
        P.Maker,
        MAX(PC.Speed) AS Speed 
    FROM
        PC 
    JOIN
        Product P 
            ON P.model = PC.Model 
    WHERE
        P.maker IN (SELECT
            Product.maker 
        FROM
            Product 
        WHERE
            Product.type = 'Printer') 
        AND PC.Ram = (SELECT
            MIN(PC.ram) 
        FROM
            PC) 
    GROUP BY
        P.Maker 
    ORDER BY
        NULL) SELECT
            outp.maker 
        FROM
            outp 
        WHERE
            outp.speed = (
                SELECT
                    MAX(outp.speed) 
                FROM
                    outp
            )

Related Articles



* original question posted on StackOverflow here.