SQL JOIN to search for records that do not have a matching value record

Question

I'm trying to speed up work on some code that I wrote many years ago for my authorization application for a purchase. Basically, I have a SLOW subquery that I would like to replace JOIN (if it's faster).

When the director is registered in the application, he sees a list of purchase requests that he has not yet approved or rejected. This list is created with the following query:

SELECT * FROM SA_ORDER WHERE ORDER_ID NOT IN
    (SELECT ORDER_ID FROM SA_SIGNATURES WHERE TYPE = 'administrative director');

There are about 900 entries in sa_order and 1800 entries in sa_signature, and this request still takes about 5 seconds. I tried using LEFT JOIN to get the records I needed, but I managed to get sa_order records with NO matching records in sa_signature, and I needed sa_order records with "no matching records with the type of" administrative director ",". Your help is much appreciated!

The schema for the two tables is as follows:

The following tables have the following layout:

CREATE TABLE sa_order
(
    `order_id`        BIGINT       PRIMARY KEY AUTO_INCREMENT,
    `order_number`    BIGINT       NOT NULL,
    `submit_date`     DATE         NOT NULL,
    `vendor_id`       BIGINT       NOT NULL,
    `DENIED`          BOOLEAN      NOT NULL DEFAULT FALSE,
    `MEMO`            MEDIUMTEXT,
    `year_id`         BIGINT       NOT NULL,
    `advisor`         VARCHAR(255) NOT NULL,
    `deleted`         BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE sa_signature
(
    `signature_id`        BIGINT          PRIMARY KEY AUTO_INCREMENT,
    `order_id`            BIGINT          NOT NULL,
    `signature`           VARCHAR(255)    NOT NULL,
    `proxy`               BOOLEAN         NOT NULL DEFAULT FALSE,
    `timestamp`           TIMESTAMP       NOT NULL DEFAULT NOW(),
    `username`            VARCHAR(255)    NOT NULL,
    `type`                VARCHAR(255)    NOT NULL
);

Answer 1

Create an index on sa_signatures (type, order_id).

, LEFT JOIN, sa_signatures null order_id. NOT IN. :

SELECT  o.*
FROM    sa_order o
LEFT JOIN
        sa_signatures s
ON      s.order_id = o.order_id
        AND s.type = 'administrative director'
WHERE   s.type IS NULL

NOT NULL sa_signatures WHERE, .

Answer 2

[NOT] IN EXISTS .

, :

SELECT * FROM SA_ORDER WHERE NOT EXISTS
    (SELECT ORDER_ID FROM SA_SIGNATURES
     WHERE TYPE = 'administrative director'
       AND ORDER_ID = SA_ORDER.ORDER_ID);

: " " NOT IN " , " NOT EXISTS " ."

: http://decipherinfosys.wordpress.com/2007/01/21/32/

Answer 3

, , , . SA_SGINATURES ORDER_ID.

SELECT * 
FROM 
SA_ORDER 
LEFT JOIN
SA_SIGNATURES
ON
SA_ORDER.ORDER_ID = SA_SIGNATURES.ORDER_ID AND
TYPE = 'administrative director'
WHERE 
SA_SIGNATURES.ORDER_ID IS NULL;

Answer 4

select * from sa_order as an internal connection sa_signature as s on o.orderid = sa.orderid and sa.type = 'administrative director'

you can also create a non-clustered index for a type in the sa_signature table

even better is to have a main table for types with typeid and typename, and then instead of saving the type as text in your sa_signature table, just save the type as a whole. this is because the calculation on integers is faster than the calculation on the text