MySQL - ускорение запросов, исключение сортировки файлов и временное

Мой запрос MySQL медленный У меня есть 3 таблицы: вакансии (200 тыс. Записей), местоположения (300 тыс.), Должности (700 тыс.).

SELECT
    j.job_offerid
FROM `job_offer` AS j 
INNER JOIN `job_offer_localitymap` AS d ON d.`job_offerid` = j.`job_offerid` AND 
    `gps_localityid` IN(35, 3301, 3302, 3303, 3305, 3306, 3307, 3308, 124, 3811, 3805, 3709, 3808, 3809) 
WHERE 
    j.`status` = 1 AND 
    j.`job_offerid` IN(
        SELECT `job_offerid` 
        FROM `job_offer_positionmap` 
        WHERE `cb_job_positionid` IN (1001, 6, 629, 7, 8, 9, 10, 11, 12, 13, 1, 15, 16, 17))
ORDER BY j.`job_offerid` DESC 
LIMIT 3

Я должен фильтровать позиции и местности, поэтому я использовал IN.

ОБЪЯСНИТЕ: Используя где; Использование индекса; Используя временные; Использование сортировки файлов; Начать временный

Схема таблицы только с используемыми строками:

CREATE TABLE `job_offer` (
  `job_offerid` int(13) NOT NULL AUTO_INCREMENT,
  `status` int(13) NOT NULL DEFAULT '1',
  PRIMARY KEY (`job_offerid`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `job_offer_localitymap` (
  `job_offer_localitymapid` int(13) NOT NULL AUTO_INCREMENT,
  `gps_localityid` int(13) NOT NULL,
  `job_offerid` int(13) NOT NULL,
  PRIMARY KEY (`job_offer_localitymapid`),
  KEY `gps_localityid` (`gps_localityid`),
  KEY `job_offerid` (`job_offerid`),
  KEY `gps_localityid_job_offerid` (`gps_localityid`,`job_offerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `job_offer_positionmap` (
  `job_offer_positionmapid` int(13) NOT NULL AUTO_INCREMENT,
  `cb_job_positionid` int(13) NOT NULL,
  `job_offerid` int(13) NOT NULL,
  PRIMARY KEY (`job_offer_positionmapid`),
  KEY `cb_job_positionid` (`cb_job_positionid`),
  KEY `job_offerid` (`job_offerid`),
  KEY `cb_job_positionid_job_offerid` (`cb_job_positionid`,`job_offerid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Индексы есть везде.

Спасибо за любой совет

Источник

2 ответа

Решение

Ваше объединение выиграет от составного

job_offer_localitymap.(job_offerid,gps_localityid)

То есть, идти противоположным путем, чем ваш нынешний композит в этой таблице.

Таким образом, вы можете отбросить эти два:

  KEY `gps_localityid` (`gps_localityid`),
  KEY `job_offerid` (`job_offerid`),

Как вы тогда оставили бы с двумя составными индексами, с большинством левого из каждого, используемого другими запросами, которые выиграли от вышеупомянутых двух, я только что сказал, чтобы отбросить

В строке запроса 5 будьте последовательны и используйте псевдоним j как я должен был охотиться (не долго), чтобы увидеть, какой стол

На мой взгляд, ключ status (status) в job_offer может быть относительно бесполезным, но я не знаю вас других запросов. Но поскольку у вас тонкие типы данных, составной элемент job_offer(job_offerid,status) может заставить многие ваши запросы работать, как это было бы covering index не нужно идти за страницей данных.

Что касается job_offer_positionmap, это может быть объединение, удаляющее медленный подзапрос и выбор разработчика для добавления там композита. Концептуально это соединение похоже на первое.

Я не вижу никаких проблем с предложениями in в целом, так как оптимизатор mysql CBO должен решать эту проблему.

Но это всего лишь предложения, поскольку добавление индексов не лишено недостатков. Это хрупкое уравновешивание, но, в конце концов, вы можете обнаружить, что этот запрос не только летает, но и ваши другие.

Источник

Вы используете join для фильтрации. Так что я бы перенес эту логику в where пункт:

SELECT j.job_offerid
FROM `job_offer` 
WHERE j.`status` = 1 AND 
      j.`job_offerid` IN (SELECT jop.`job_offerid` 
                          FROM `job_offer_positionmap` jop
                          WHERE `cb_job_positionid` IN (1001, 6, 629, 7, 8, 9, 10, 11, 12, 13, 1, 15, 16, 17)
                         ) AND
      j.`job_offerid` IN (SELECT jop.`job_offerid` 
                          FROM `job_offer_localitymap` jol
                          WHERE jol.gps_localityid IN (35, 3301, 3302, 3303, 3305, 3306, 3307, 3308, 124, 3811, 3805, 3709, 3808, 3809)
                         )                        
ORDER BY j.`job_offerid` DESC 
LIMIT 3;

Затем для этого запроса вы хотите следующие индексы:

  • job_offer (статус, job_offerid desc)
  • job_offer_positionmap (cb_job_positionid, job_offerid)
  • job_offer_localitymap (gps_localityid, job_offerid)

Результирующий запрос должен использовать первый индекс для фильтрации и order by пункт. Затем он будет использовать два других индекса для фильтрации.

Источник
Другие вопросы по тегам