Как настроиться, MySQL с 7 соединениями таблицы считают запрос, где таблицы содержат 30,000 + строки?

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

Это идея, аналогичная предложенной Донни, но я немного привел ее в порядок. Если я правильно понял объединения, нет необходимости повторять все объединения при каждом выборе, поскольку каждое фактически независимо от других. Исходный пункт WHERE оговаривает, что gallery.published должен быть истинным, а затем следует ряд из 4 условий, соединенных OR . Таким образом, каждый запрос может выполняться отдельно. Вот четыре соединения:

gallery <--> gallery_to_name <--> name
gallery <--> gallery_to_tag <--> tag
gallery <--> site
gallery <--> site <--> site_to_tag <--> tag

Поскольку галерея содержит site_id , в этом случае нет необходимости в промежуточном соединении через таблицу site . Таким образом, последнее соединение может быть сокращено до следующего:

gallery <--> site_to_tag <--> tag

Выполнение каждого SELECT отдельно и использование UNION для объединения результатов выполняется очень быстро. Результаты здесь предполагают структуры таблиц и индексы , показанные в конце этого сообщения:

SELECT SQL_NO_CACHE COUNT(id) AS matches FROM (
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN site AS s ON s.id = g.site_id
    WHERE g.published = TRUE AND s.name LIKE '3GRD%')
UNION
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN gallery_to_name AS g2n ON g2n.gallery_id = g.id
    INNER JOIN name AS n ON n.id = g2n.name_id
    WHERE g.published = TRUE AND n.value LIKE '3GRD%')
UNION
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN gallery_to_tag  AS g2t ON g2t.gallery_id = g.id
    INNER JOIN tag AS gt  ON gt.id = g2t.tag_id
    WHERE g.published = TRUE AND gt.term = '3GRD')
UNION
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN site_to_tag AS s2t ON s2t.site_id = g.site_id
    INNER JOIN tag AS st  ON st.id = s2t.tag_id
    WHERE g.published = TRUE AND st.term = '3GRD')
) AS totals;

+---------+
| matches |
+---------+
|      99 |
+---------+
1 row in set (0.00 sec)

Скорость действительно зависит от критериев поиска. В следующем примере для каждой таблицы используется различное значение поиска, и оператору LIKE нужно проделать немного больше работы, поскольку теперь существует больше потенциальных совпадений для каждой:

SELECT SQL_NO_CACHE COUNT(id) AS matches FROM (
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN site AS s ON s.id = g.site_id
    WHERE g.published = TRUE AND s.name LIKE '3H%')
UNION
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN gallery_to_name AS g2n ON g2n.gallery_id = g.id
    INNER JOIN name AS n ON n.id = g2n.name_id
    WHERE g.published = TRUE AND n.value LIKE '3G%')
UNION
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN gallery_to_tag  AS g2t ON g2t.gallery_id = g.id
    INNER JOIN tag AS gt  ON gt.id = g2t.tag_id
    WHERE g.published = TRUE AND gt.term = '3IDP')
UNION
   (SELECT g.id
    FROM gallery AS g
    INNER JOIN site_to_tag AS s2t ON s2t.site_id = g.site_id
    INNER JOIN tag AS st  ON st.id = s2t.tag_id
    WHERE g.published = TRUE AND st.term = '3OJX')
) AS totals;

+---------+
| matches |
+---------+
|   12505 |
+---------+
1 row in set (0.24 sec)

Эти результаты выгодно отличаются от результатов запроса, который использует несколько объединений:

SELECT SQL_NO_CACHE COUNT(DISTINCT g.id) AS matches
FROM gallery AS g
INNER JOIN gallery_to_name AS g2n ON g2n.gallery_id = g.id
INNER JOIN name            AS n   ON n.id = g2n.name_id
INNER JOIN gallery_to_tag  AS g2t ON g2t.gallery_id = g.id
INNER JOIN tag             AS gt  ON gt.id = g2t.tag_id
INNER JOIN site            AS s   ON s.id = g.site_id
INNER JOIN site_to_tag     AS s2t ON s2t.site_id = s.id
INNER JOIN tag             AS st  ON st.id = s2t.tag_id
WHERE g.published = TRUE AND (
    gt.term = '3GRD' OR
    st.term = '3GRD' OR
    n.value LIKE '3GRD%' OR
    s.name LIKE '3GRD%');

+---------+
| matches |
+---------+
|      99 |
+---------+
1 row in set (2.62 sec)

SELECT SQL_NO_CACHE COUNT(DISTINCT g.id) AS matches
FROM gallery AS g
INNER JOIN gallery_to_name AS g2n ON g2n.gallery_id = g.id
INNER JOIN name            AS n   ON n.id = g2n.name_id
INNER JOIN gallery_to_tag  AS g2t ON g2t.gallery_id = g.id
INNER JOIN tag             AS gt  ON gt.id = g2t.tag_id
INNER JOIN site            AS s   ON s.id = g.site_id
INNER JOIN site_to_tag     AS s2t ON s2t.site_id = s.id
INNER JOIN tag             AS st  ON st.id = s2t.tag_id
WHERE g.published = TRUE AND (
    gt.term = '3IDP' OR
    st.term = '3OJX' OR
    n.value LIKE '3G%' OR
    s.name LIKE '3H%');

+---------+
| matches |
+---------+
|   12505 |
+---------+
1 row in set (3.17 sec)

SCHEMA
Важны индексы столбцов id плюс site.name , name.value и tag.term :

DROP SCHEMA IF EXISTS `egervari`;
CREATE SCHEMA IF NOT EXISTS `egervari`;
USE `egervari`;

-- -----------------------------------------------------
-- Table `site`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `site` ;
CREATE  TABLE IF NOT EXISTS `site` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  INDEX `name` (`name` ASC) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `gallery`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `gallery` ;
CREATE  TABLE IF NOT EXISTS `gallery` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `site_id` INT UNSIGNED NOT NULL ,
  `published` TINYINT(1) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_gallery_site` (`site_id` ASC) ,
  CONSTRAINT `fk_gallery_site`
    FOREIGN KEY (`site_id` )
    REFERENCES `site` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `name`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `name` ;
CREATE  TABLE IF NOT EXISTS `name` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `value` VARCHAR(255) NOT NULL ,
  INDEX `value` (`value` ASC) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `tag`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `tag` ;
CREATE  TABLE IF NOT EXISTS `tag` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `term` VARCHAR(255) NOT NULL ,
  INDEX `term` (`term` ASC) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `gallery_to_name`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `gallery_to_name` ;
CREATE  TABLE IF NOT EXISTS `gallery_to_name` (
  `gallery_id` INT UNSIGNED NOT NULL ,
  `name_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`gallery_id`, `name_id`) ,
  INDEX `fk_gallery_to_name_gallery` (`gallery_id` ASC) ,
  INDEX `fk_gallery_to_name_name` (`name_id` ASC) ,
  CONSTRAINT `fk_gallery_to_name_gallery`
    FOREIGN KEY (`gallery_id` )
    REFERENCES `gallery` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_gallery_to_name_name`
    FOREIGN KEY (`name_id` )
    REFERENCES `name` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `gallery_to_tag`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `gallery_to_tag` ;
CREATE  TABLE IF NOT EXISTS `gallery_to_tag` (
  `gallery_id` INT UNSIGNED NOT NULL ,
  `tag_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`gallery_id`, `tag_id`) ,
  INDEX `fk_gallery_to_tag_gallery` (`gallery_id` ASC) ,
  INDEX `fk_gallery_to_tag_tag` (`tag_id` ASC) ,
  CONSTRAINT `fk_gallery_to_tag_gallery`
    FOREIGN KEY (`gallery_id` )
    REFERENCES `gallery` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_gallery_to_tag_tag`
    FOREIGN KEY (`tag_id` )
    REFERENCES `tag` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `site_to_tag`
-- -----------------------------------------------------

DROP TABLE IF EXISTS `site_to_tag` ;
CREATE  TABLE IF NOT EXISTS `site_to_tag` (
  `site_id` INT UNSIGNED NOT NULL ,
  `tag_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`site_id`, `tag_id`) ,
  INDEX `fk_site_to_tag_site` (`site_id` ASC) ,
  INDEX `fk_site_to_tag_tag` (`tag_id` ASC) ,
  CONSTRAINT `fk_site_to_tag_site`
    FOREIGN KEY (`site_id` )
    REFERENCES `site` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_site_to_tag_tag`
    FOREIGN KEY (`tag_id` )
    REFERENCES `tag` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

] ТЕСТОВЫЕ ДАННЫЕ
Заполняются сайт 900 строками, тег 3560 строками, имя 800 строками и галерея 40 000 строк и вставляет записи в таблицы ссылок:

DELIMITER //
DROP PROCEDURE IF EXISTS populate//
CREATE PROCEDURE populate()
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < 900 DO
        INSERT INTO site (name) VALUES (CONV(i + 1 * 10000, 20, 36));
        SET i = i + 1;
    END WHILE;

    SET i = 0;
    WHILE i < 3560 DO
        INSERT INTO tag (term) VALUES (CONV(i + 1 * 10000, 20, 36));
        INSERT INTO site_to_tag (site_id, tag_id) VALUES ( (i MOD 900) + 1, i + 1 );
        SET i = i + 1;
    END WHILE;

    SET i = 0;
    WHILE i < 800 DO
        INSERT INTO name (value) VALUES (CONV(i + 1 * 10000, 20, 36));
        SET i = i + 1;
    END WHILE;

    SET i = 0;
    WHILE i < 40000 DO    
        INSERT INTO gallery (site_id, published) VALUES ( (i MOD 900) + 1, i MOD 2 );
        INSERT INTO gallery_to_name (gallery_id, name_id) VALUES ( i + 1, (i MOD 800) + 1 );
        INSERT INTO gallery_to_tag (gallery_id, tag_id) VALUES ( i + 1, (i MOD 3560) + 1 );
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;
CALL populate();