Tengo esta consulta para recopilar la información sobre un solo pedido y se ha vuelto bastante compleja.

No tengo datos para probar, así que estoy preguntando: si alguien tiene experiencia con esto en conjuntos de datos pequeños y grandes, ¿hay un límite de cuántas uniones puede o debe hacer en una sola consulta? ¿Sería recomendable dividir las consultas grandes en partes más pequeñas o esto no supone una diferencia significativa?

Además, ¿es legal tener unWHERE cláusula después de cadaINNER JOIN?

Gracias por su consejo.

Aquí está la consulta:

<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>

Como alguien solicitó el esquema para esta consulta, aquí está:

<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>