MySQL ¿Hay un límite para InnerJoin?
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>