MySQL Есть ли предел для InnerJoin?

У меня есть этот запрос для сбора информации об одном заказе, и он становится довольно сложным.

У меня нет данных для тестирования, поэтому я спрашиваю, есть ли у кого-нибудь опыт работы с этим в малых и больших наборах данных, существует ли ограничение на количество соединений, которые вы можете или должны сделать в одном запросе? Было бы целесообразно разделить большие запросы на более мелкие части или это не имеет существенного значения?

Кроме того, законно ли иметьWHERE пункт после каждогоINNER JOIN?

Спасибо за совет.

Вот запрос:

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

Поскольку кто-то запросил схему для этого запроса, вот она:

<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>
 hakre14 апр. 2012 г., 16:52
Нажмите на макс. Пока вы не получаете никаких синтаксических ошибок или чего-то вроде "слишком много объединений", продолжайте.
 Ozzy14 апр. 2012 г., 17:05
@hakre Спасибо ... я так понимаю, размер запроса не имеет значения.

Ответы на вопрос(4)

Решение Вопроса

Вы не имеете где-либо близко к пределу JOIN для MySQL. Ваше количество объединений не является плохим. Однако присоединение к производной таблице (вашему внутреннему подзапросу), как вы это делаете, может вызвать проблемы с производительностью, поскольку производные таблицы не имеют индексов. Выполнение объединения на производной таблице без индексов может быть медленным.

Вам следует подумать о создании реальной временной таблицы с индексами для объединения или найти способ избежать подзапроса.

JOIN в MySQL - это как поиск (поиск) для каждой объединенной строки. Таким образом, MySQL придется выполнять много поисков, если вы объединяете много записей. Это меньше о количестве таблиц, к которым вы присоединяетесь, чем о количестве строк, к которым вы присоединяетесь, которые могут быть проблемой.

В любом случае, MySQL будет выполнять столько запросов, пока не сдастся и просто прочитает всю таблицу. Это делает довольно хорошую работу при принятии решения, которое будет дешевле.

Возможно, лучшее, что вы можете сделать, это помочь ему угадать, обновив статистику индекса с помощью ANALYZE TABLE.

Вы можете иметь одно предложение WHERE на SELECT. Таким образом, ваш внутренний подзапрос будет иметь предложение WHERE, а ваш внешний запрос будет иметь предложение WHERE, и они будут применены после JOIN (по крайней мере, логически, хотя MySQL обычно применяет их в первую очередь для производительности).

Кроме того, все это предполагает, что вы знаете, как правильно использовать индексы.

 Ozzy14 апр. 2012 г., 20:09
Я рад принять этот ответ. Я мог бы просто создать другую таблицу (как вы предложили), если у меня есть проблемы с производительностью, но я пока оставлю ее как есть. У меня пока нет сведений об индексации ... Мне придется прочитать некоторые статьи. Спасибо @ Маркус-Адамс

И & quot; как часть JOIN.

INNER JOIN kopplaMedlemIntresse 
ON intressen.id = kopplaMedlemIntresse.intresse_id AND kopplaMedlemIntresse.medlem_id = 3

Однажды у меня была проблема с очень плохой производительностью, когда я добавил тонны таблиц, используя JOIN. Было около 20 JOIN, я сделал. Когда я удалил несколько JOINS в тесте, скорость снова возросла. В связи с тем, что мне просто нужна была единственная информация, я смог заменить большинство JOINS суб-выборами. Это решило мою проблему с 25 секунд для моего запроса до менее чем 1 секунды.

Вероятно, это факт дизайна вашей таблицы, количества столбцов в соединяемых таблицах и ваших индексов в объединенных выражениях where.

 14 апр. 2012 г., 17:37
Я цитирую MySQL документацию поcorrelated subqueries: "... они неэффективны и могут быть медленными. Переписать запрос как объединение может повысить производительность. & Quot; Документы согласны с вами, что в некоторых случаях они могут быть оптимизированы. Не больно попробовать, верно? :)
 14 апр. 2012 г., 17:19
Я думаю, что это зависит от самого запроса. В моем случае саб выбирает куда быстрее с правильной настройкой индекса.
 14 апр. 2012 г., 17:19
Я думаю, что вы должны присоединиться ко всему, что вам нужно, а затем начать упрощать его (суб выбирает только для данных столбца 1 и объединяет те же данные)
 Ozzy14 апр. 2012 г., 16:56
То есть вы говорите, что у меня могут быть большие запросы, но sub-SELECT намного быстрее, чем синтаксис JOIN?

однажды я попробовал сам, чтобы увидеть ограничение на количество объединений, и я проверил объединение с 100 таблицами в MySQL 5.0.4, если я правильно его помню.

Мне дали следующую ошибку:

Too many tables. MySQL can only use 61 tables in a join.

Я думаю, что предел для MySql 4 был 31 стол.

Совет, если вы планируете использовать столько таблиц в запросе, то самое время подумать об упрощении структуры запроса.

 Ozzy14 апр. 2012 г., 20:02
+1 за информацию о лимите (спасибо). Я думаю, что информация ставит это в перспективу для меня, мои 8 соединений из возможных 61 не могут быть плохими вообще.

Ваш ответ на вопрос