MySQL Gibt es ein Limit für InnerJoin?
Ich habe diese Abfrage, um die Informationen zu einer einzelnen Bestellung zu sammeln, und sie sind recht komplex geworden.
Ich habe keine Daten zum Testen, daher frage ich mich, ob jemand Erfahrung mit kleinen und großen Datenmengen hat. Gibt es eine Grenze für die Anzahl der Verknüpfungen, die Sie in einer einzelnen Abfrage ausführen können oder sollten? Wäre es ratsam, die großen Abfragen in kleinere Teile aufzuteilen, oder macht dies keinen wesentlichen Unterschied?
Ist es auch legal eine zu haben?WHERE
Klausel nach jedemINNER JOIN
?
Danke für deinen Rat.
Hier ist die Abfrage:
<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>
Da jemand das Schema für diese Abfrage angefordert hat, ist es hier:
<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>