Как ускорить запрос MySQL с несколькими объединениями

Вот моя проблема, я выбираю и делаю несколько объединений для получения правильных элементов... он тянет в большом количестве строк, выше 100 000. Этот запрос занимает более 5 минут, если для диапазона дат установлено значение 1 год.

Я не знаю, возможно ли это, но я боюсь, что пользователь может расширить диапазон дат до десяти лет и свернуть его.

Кто-нибудь знает, как я могу ускорить это? Вот запрос.

SELECT DISTINCT t1.first_name, t1.last_name, t1.email 
FROM table1 AS t1 
INNER JOIN table2 AS t2 ON t1.CU_id = t2.O_cid 
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref 
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id 
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id 
WHERE t1.subscribe =1 
AND t1.Cdate >= $startDate
AND t1.Cdate <= $endDate
AND t5.store =2

Я не самый лучший с mysql, поэтому любая помощь будет оценена!

Спасибо заранее!

UPDATE

Вот объяснение, которое вы просили

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  t5  ref     PRIMARY,C_store_type,C_id,C_store_type_2    C_store_type_2  1   const   101     Using temporary
1   SIMPLE  t4  ref     PRIMARY,P_cat   P_cat   5   alphacom.t5.C_id    326     Using where
1   SIMPLE  t3  ref     I_pid,I_oref    I_pid   4   alphacom.t4.P_id    31   
1   SIMPLE  t2  eq_ref  O_ref,O_cid     O_ref   28  alphacom.t3.I_oref  1    
1   SIMPLE  t1  eq_ref  PRIMARY     PRIMARY     4   alphacom.t2.O_cid   1   Using where

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

Ответ 1

Я бы попробовал следующее:

Сначала убедитесь, что есть индексы для следующих таблиц и столбцов (каждый набор столбцов в круглых скобках должен быть отдельным индексом):

table1 : (subscribe, CDate)
         (CU_id)
table2 : (O_cid)
         (O_ref)
table3 : (I_oref)
         (I_pid)
table4 : (P_id)
         (P_cat)
table5 : (C_id, store)

Во-вторых, , если добавление указанных индексов не улучшало все, что угодно, попробуйте переписать запрос как

SELECT DISTINCT t1.first_name, t1.last_name, t1.email FROM
  (SELECT CU_id, t1.first_name, t1.last_name, t1.email
     FROM table1
     WHERE subscribe = 1 AND
           CDate >= $startDate AND
           CDate <= $endDate) AS t1
  INNER JOIN table2 AS t2
    ON t1.CU_id = t2.O_cid   
  INNER JOIN table3 AS t3
    ON t2.O_ref = t3.I_oref   
  INNER JOIN table4 AS t4
    ON t3.I_pid = t4.P_id   
  INNER JOIN (SELECT C_id FROM table5 WHERE store = 2) AS t5
    ON t4.P_cat = t5.C_id

Я надеюсь, что первый суб-выбор значительно сократит количество строк, которые будут рассматриваться для присоединения, надеюсь, что последующие объединения будут меньше работать. То же самое можно сказать по поводу второго подвыбора на таблице 5.

В любом случае, беспорядок с ним. Я имею в виду, в конечном счете, это просто ВЫБОР - вы не можете ничего на нее нанести. Изучите планы, которые генерируются каждой другой перестановкой, и попытайтесь выяснить, что хорошо или плохо в каждом.

Поделитесь и наслаждайтесь.

Ответ 2

Удостоверьтесь, что индексируются столбцы даты и все столбцы, на которые вы соединяетесь.

Выполнение оператора неэквивалентности в ваших датах означает, что он проверяет каждую строку, которая по своей природе медленнее эквивалентности.

Кроме того, использование DISTINCT добавляет дополнительное сравнение с логикой, что ваш оптимизатор работает за кулисами. Устраните это, если это возможно.

Ответ 3

Ну, во-первых, сделайте подзапрос, чтобы децитировать таблицу1 вплоть до тех записей, которые вы на самом деле хотите, чтобы все проблемы с присоединением...

SELECT DISTINCT t1.first_name, t1.last_name, t1.email  
FROM (  
SELECT first_name, last_name, email, CU_id FROM table1 WHERE  
table1.subscribe = 1  
AND table1.Cdate >= $startDate  
AND table1.Cdate <= $endDate  
) AS t1  
INNER JOIN table2 AS t2 ON t1.CU_id = t2.O_cid  
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref  
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id  
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id  
WHERE t5.store = 2

Затем начните смотреть на изменение направленности соединений.

Кроме того, если t5.store только очень редко 2, то переверните эту идею вокруг: постройте подзапрос t5, затем присоедините его назад и назад и назад.

Ответ 4

В настоящее время ваш запрос возвращает все соответствующие строки в таблице2-table5, чтобы установить, является ли t5.store = 2. Если какая-либо из таблиц2-table5 имеет значительно больший ряд строк, чем таблица1, это может значительно увеличить число обработанных строк - следовательно, следующий запрос может значительно улучшиться:

SELECT DISTINCT t1.first_name, t1.last_name, t1.email 
FROM table1 AS t1 
WHERE t1.subscribe =1 
AND t1.Cdate >= $startDate
AND t1.Cdate <= $endDate
AND EXISTS
(SELECT NULL FROM table2 AS t2
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref 
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id 
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id AND t5.store =2
WHERE t1.CU_id = t2.O_cid);

Ответ 5

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

Кроме того, это также зависит от используемого вами двигателя. Если вы используете InnoDB, проверьте свои параметры конфигурации. Я столкнулся с аналогичной проблемой, поскольку стандартная конфигурация innodb не будет масштабироваться так же, как настройка myisam по умолчанию.

Ответ 6

Как все говорят, убедитесь, что у вас есть индексы.

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

Без EXPLAIN работать не так много. Также имейте в виду, что MySQL будет смотреть ваш JOIN и выполнять все возможные решения перед выполнением запроса, что может занять некоторое время. После того, как у вас будет оптимальный порядок JOIN от EXPLAIN, вы можете попытаться заставить этот порядок в своем запросе, исключив этот шаг от оптимизатора.

Ответ 7

Похоже, вы должны подумать о доставке подмножеств (подкачки) или ограничить результаты каким-либо другим способом, если нет причины, по которой пользователям нужна каждая строка, возможно, сразу. Обычно 100 тыс. Строк больше, чем может усвоить средний человек.