Mam to zapytanie, aby zebrać informacje o pojedynczym zamówieniu i stało się ono dość złożone.

Nie mam żadnych danych do przetestowania, więc pytam, czy ktoś ma doświadczenie z tym w małych i dużych zestawach danych, czy istnieje ograniczenie liczby przyłączeń, które można lub należy wykonać w jednym zapytaniu? Czy wskazane byłoby podzielenie dużych zapytań na mniejsze części, czy też nie ma to większego znaczenia?

Czy to jest legalneWHERE klauzula po każdymINNER JOIN?

Dzięki za radę.

Oto zapytanie:

<code># Order: Get Order

function getOrder($order_id) {
    $sql = "SELECT (order.id, order.created, o_status.status,
                    /* payment info */
                    order.total, p_status.status,
                    /* ordered by */
                    cust_title.title, cust.forename, cust.surname,
                    customer.phone, customer.email,
                    cust.door_name, cust.street1,
                    cust.street2, cust.town,
                    cust.city, cust.postcode,
                    /* deliver to */
                    recip_title.title, recipient.forename, recipient.surname,
                    recipient.door_name, recipient.street1,
                    recipient.street2, recipient.town,
                    recipient.city, recipient.postcode,
                    /* deliver info */
                        shipping.name, order.memo,
                    /* meta data */
                    order.last_update)
              FROM tbl_order AS order

        INNER JOIN tbl_order_st AS o_status
                ON order.order_status_id = o_status.id

        INNER JOIN tbl_payment_st AS p_status
                ON order.payment_status_id = p_status.id

        INNER JOIN (SELECT (cust_title.title, cust.forename, cust.surname,
                            customer.phone, customer.email,
        /* ordered by */    cust.door_name, cust.street1,
                            cust.street2, cust.town,
                            cust.city, cust.postcode)
                      FROM tbl_customer AS customer
                INNER JOIN tbl_contact AS cust
                          ON customer.contact_id = cust.id
                INNER JOIN tbl_contact_title AS cust_title
                        ON cust.contact_title_id = cust_title.id
                     WHERE order.customer_id = customer.id)
                ON order.customer_id = customer.id

        INNER JOIN (SELECT (recip_title.title, recipient.forename, recipient.surname,
        /* deliver to */    recipient.door_name, recipient.street1,
                            recipient.street2, recipient.town,
                            recipient.city, recipient.postcode)
                      FROM tbl_contact AS recipient
                INNER JOIN tbl_contact_title AS recip_title
                        ON recipient.contact_title_id = recip_title.id
                     WHERE order.contact_id = recipient.id)
                ON order.contact_id = recipient.id

        INNER JOIN tbl_shipping_opt AS shipping
                ON order.shipping_option_id = shipping.id

             WHERE order.id = '?';";
    dbQuery($sql, array((int)$order_id));
    $rows = dbRowsAffected();
    if ($rows == 1)
        return dbFetchAll();
    else
        return null;
}
</code>

Ponieważ ktoś zażądał schematu dla tego zapytania, tutaj:

<code># TBL_CONTACT_TITLE

DROP TABLE IF EXISTS tbl_contact_title;
CREATE TABLE tbl_contact_title(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    title CHAR(3)
) ENGINE = InnoDB;
INSERT INTO tbl_contact_title
    (title)
VALUES  ('MR'),
    ('MRS'),
    ('MS');


# TBL_CONTACT

DROP TABLE IF EXISTS tbl_contact;
CREATE TABLE tbl_contact(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    contact_title_id INT,
    FOREIGN KEY(contact_title_id) REFERENCES tbl_contact_title(id) ON DELETE SET NULL,
    forename VARCHAR(50),
    surname VARCHAR(50),
    door_name VARCHAR(25),
    street1 VARCHAR(40),
    street2 VARCHAR(40),
    town VARCHAR(40),
    city VARCHAR(40),
    postcode VARCHAR(10),
    currency_id INT,
    FOREIGN KEY(currency_id) REFERENCES tbl_currency(id) ON DELETE SET NULL
) ENGINE = InnoDB;

# TBL_CUSTOMER

DROP TABLE IF EXISTS tbl_customer;
CREATE TABLE tbl_customer(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    contact_id INT,
    FOREIGN KEY(contact_id) REFERENCES tbl_contact(id) ON DELETE SET NULL,
    birthday DATE,
    is_male TINYINT,
    phone VARCHAR(20),
    email VARCHAR(50) NOT NULL
) ENGINE = InnoDB, AUTO_INCREMENT = 1000;

# TBL_ORDER_ST

DROP TABLE IF EXISTS tbl_order_st;
CREATE TABLE tbl_order_st(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    status VARCHAR(25)
) ENGINE = InnoDB;
INSERT INTO tbl_order_st
    (status)
VALUES
    ('NEW'),
    ('PROCESSING'),
    ('SHIPPED'),
    ('COMPLETED'),
    ('CANCELLED');


# TBL_SHIPPING_OPT

DROP TABLE IF EXISTS tbl_shipping_opt;
CREATE TABLE tbl_shipping_opt(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    name VARCHAR(50),
    description VARCHAR(255),
    cost DECIMAL(6,3)
) ENGINE = InnoDB;
INSERT INTO tbl_shipping_opt
    (name, description, cost)
VALUES
    ('UK Premier', 'U.K. Mainland upto 30KG, Next Working Day', 8.00),
    ('Europe Standard', 'Most European Destinations* upto 30KG, 2 to 5 Working Days *please check before purchase', 15.00);


# TBL_PAYMENT_ST

DROP TABLE IF EXISTS tbl_payment_st;
CREATE TABLE tbl_payment_st(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    status VARCHAR(25)
) ENGINE = InnoDB;
INSERT INTO tbl_payment_st
    (status)
VALUES
    ('UNPAID'),
    ('PAID');


# TBL_ORDER

DROP TABLE IF EXISTS tbl_order;
CREATE TABLE tbl_order(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    customer_id INT,
        FOREIGN KEY(customer_id) REFERENCES tbl_customer(id) ON DELETE SET NULL,
    contact_id INT,
    FOREIGN KEY(contact_id) REFERENCES tbl_contact(id) ON DELETE SET NULL,
    created DATETIME,
    last_update TIMESTAMP,
    memo VARCHAR(255),
    order_status_id INT,
    FOREIGN KEY(order_status_id) REFERENCES tbl_order_st(id),
    shipping_option_id INT,
    FOREIGN KEY(shipping_option_id) REFERENCES tbl_shipping_opt(id),
    coupon_id INT,
    FOREIGN KEY(coupon_id) REFERENCES tbl_coupon(id) ON DELETE SET NULL,
    total DECIMAL(9,3),
    payment_status_id INT,
    FOREIGN KEY(payment_status_id) REFERENCES tbl_payment_st(id)
) ENGINE = InnoDB, AUTO_INCREMENT = 1000;
</code>