Databases
StackOverflow2013
Postsdbo
POMySQL Is there a limit to InnerJoin?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Is there a limit to InnerJoin?
    primarykey
    data
    text
    <p>I have this query for gathering the information about a single order and its become quite complex.</p> <p>I don't have any data to test with so i'm asking, if anyone has experience with this in small and large data sets, is there a limit to how many joins you can or should make in a single query? Would it be advisable to split the large queries into smaller parts or does this not make a significant difference?</p> <p>Also, is it legal to have a <code>WHERE</code> clause after each <code>INNER JOIN</code>?</p> <p>Thanks for your advice.</p> <p>Here is the query:</p> <pre><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></pre> <p>Since someone requested the schema for this query, here it is:</p> <pre><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></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOzzy
    1. USOzzy
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL Is there a limit to InnerJoin?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL Is there a limit to InnerJoin?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL Is there a limit to InnerJoin?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPush it to the max. As long as you don't get any syntax errors or something like "too many joins", go ahead.
      singulars
      1. POMySQL Is there a limit to InnerJoin?
      1. UShakre
    2. CO@hakre Thanks... I take it then, the size of the query doesn't matter.
      singulars
      1. POMySQL Is there a limit to InnerJoin?
      1. USOzzy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload