[Solved] AM I on the right path

EverSQL Database Performance Knowledge Base

AM I on the right path

I'm taking a database intro master's class. We are working on SQL. The professor likes to be ambiguous with certain explains.

Here's my question. Certain questions we are required to find out the opposite of a query something like if a supplier ships parts that are red and blue what colors don't the ship.

here is how I figured out a solution

SELECT distinct PARTS.COLOR
FROM PARTS, SHIPMENTS
WHERE PARTS.COLOR NOT IN(
  SELECT distinct PARTS.COLOR
  FROM SHIPMENTS, PARTS
  WHERE PARTS.PARTNO  IN(
    SELECT distinct SHIPMENTS.PARTNO
    FROM SHIPMENTS
    WHERE SHIPMENTS.SUPPLIERNO='S1'))
AND SHIPMENTS.PARTNO = PARTS.PARTNO;

What I was wondering is, is this best approach to this question. This works but I'm not sure it is how it should be done.

I should also mention he does not want us to use all available operations. He did not show us JOIN, EXISTS,

he showed us SELECT, IN, ALL/ANY, Aggregates so MAX, MIN, SUM, GROUP BY, and HAVING

Thanks

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. 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.
  2. Replace In Subquery With Correlated Exists (modified query below): In many cases, an EXISTS subquery with a correlated condition will perform better than a non correlated IN subquery.
Optimal indexes for this query:
ALTER TABLE `PARTS` ADD INDEX `parts_idx_partno` (`PARTNO`);
ALTER TABLE `PARTS` ADD INDEX `parts_idx_color` (`COLOR`);
ALTER TABLE `SHIPMENTS` ADD INDEX `shipments_idx_partno` (`PARTNO`);
ALTER TABLE `SHIPMENTS` ADD INDEX `shipments_idx_supplierno_partno` (`SUPPLIERNO`,`PARTNO`);
The optimized query:
SELECT
        DISTINCT PARTS.COLOR 
    FROM
        PARTS,
        SHIPMENTS 
    WHERE
        NOT EXISTS (
            SELECT
                DISTINCT 1 
            FROM
                SHIPMENTS,
                PARTS AS PARTS1 
            WHERE
                (
                    EXISTS (
                        SELECT
                            DISTINCT 1 
                        FROM
                            SHIPMENTS 
                        WHERE
                            (
                                SHIPMENTS.SUPPLIERNO = 'S1'
                            ) 
                            AND (
                                PARTS1.PARTNO = SHIPMENTS.PARTNO
                            )
                    )
                ) 
                AND (
                    PARTS.COLOR = PARTS1.COLOR
                )
            ) 
            AND SHIPMENTS.PARTNO = PARTS.PARTNO

Related Articles



* original question posted on StackOverflow here.