Блог

Мой код запроса MySQL выглядит так, как показано ниже, и в таблице около нескольких тысяч записей, к настоящему времени приведенный ниже SQL выполняется около 5 минут и более. Я ищу способы оптимизировать его, чтобы выполнение занимало меньше времени. Спасибо!

 SELECT `m`.`id`,
   `m`.`id`,
   `tr`.`name`,
   `m`.`m_date`,
   `t1`.`t_name` AS home,
   `t2`.`t_name` AS away,
   `m`.`score1`,
   `m`.`score2`,
   `cw1`.`tid` AS tid1,
   `cw2`.`tid` AS tid2,
   `o1`.`odds` AS odds1,
   `o2`.`odds` AS odds2,
   `m`.`m_time`
FROM `jos_bl_match` AS `m`
LEFT JOIN `jos_bl_matchday` AS `md` ON (`md`.`id` = `m`.`m_id`)
LEFT JOIN `jos_bl_seasons` AS `s` ON (`s`.`s_id` = `md`.`s_id`)
LEFT JOIN `jos_bl_tournament` AS `tr` ON (`tr`.`id` = `s`.`t_id`)
LEFT JOIN `jos_bl_teams` AS `t1` ON (`m`.`team1_id` = `t1`.`id`)
LEFT JOIN `jos_bl_teams` AS `t2` ON (`m`.`team2_id` = `t2`.`id`)
LEFT JOIN `jos_vuvuzelaodds_odds` AS `o1` ON (`o1`.`m_id` = `m`.`id`)
AND `o1`.`market_id` = 1
AND `o1`.`bookmaker_id` = 1
LEFT JOIN `jos_vuvuzelaodds_odds` AS `o2` ON (`o2`.`m_id` = `m`.`id`)
AND `o2`.`market_id` = 1
AND `o2`.`bookmaker_id` = 2
LEFT JOIN `jos_cwtags_tags` AS `cw1` ON (`cw1`.`item_id` = `o1`.`m_id`)
LEFT JOIN `jos_cwtags_tags` AS `cw2` ON (`cw2`.`item_id` = `o2`.`m_id`)
WHERE `m`.`published` = 1
AND `s`.`published` = '1'
AND `tr`.`published` = '1'
AND `s`.`s_id` = 869
AND `m`.`m_played` = '1'
AND `m`.`m_date` > 2013-01-01
AND `o1`.`odds` != ''
AND `o2`.`odds` != ''
AND `cw1`.`cat_id` = 19
AND `cw2`.`cat_id` = 21
ORDER BY `m`.`m_date`,
     `md`.`id`,
     `s`.`s_id`,
     `tr`.`id` DESC LIMIT 0, 15
  

«Нормализуйте, но не чрезмерно нормализуйте».

Некоторые составные индексы могут отсутствовать…

 jos_bl_match: INDEX(m_played, published, m_date)
  

Столбцы должны быть в таком порядке. Это позволит быстрее запустить фильтрацию.

Следующее должно ускорить их объединение:

 jos_vuvuzelaodds_odds: INDEX(market_id, bookmaker_id, m_id)
jos_cwtags_tags: INDEX(cat_id, item_id)
  

Похоже, что эти последние два индекса могут (должны) быть PRIMARY KEY . Являются ли они?

Некоторые (возможно, все) из LEFT JOINs них также могут быть INNER JOINs ; вы это учли?

Пожалуйста, предоставьте EXPLAIN SELECT .

Без доступа к базе данных это немного сложно сказать. Кажется, что слишком много данных, чтобы извлекать только 15 записей. Вы уверены, что вам нужно извлекать данные таким образом?

Вероятно, лучший маршрут:

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

Вы не выбираете какие-либо поля из таблицы seasons в своем выборе. Я использовал поле из таблицы jos_bl_matchday.

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

 (IF value = 1, table.field, null) as yadda 
  

вместо того, чтобы присоединяться к одной таблице снова и снова, но вам придется экспериментировать.

 CREATE VIEW allTheGames AS SELECT
`m`.`id` as id,
`md`.`s_id` as seasonId, 
  `tr`.`name` as name,
 `m`.`m_date` as m_date,
 `t1`.`t_name` AS home,
 `t2`.`t_name` AS away,
`m`.`score1` as score1,
 `m`.`score2` as score2,
 `cw1`.`tid` AS tid1,
 `cw2`.`tid` AS tid2,
 `o1`.`odds` AS odds1,
 `o2`.`odds` AS odds2,
 `m`.`m_time` as m_time 
          FROM `jos_bl_match` AS `m` 
          LEFT JOIN `jos_bl_matchday` AS `md` ON (`md`.`id` = `m`.`m_id`) 
          LEFT JOIN `jos_bl_tournament` AS `tr` ON (`tr`.`id` = `s`.`t_id`) 
          LEFT JOIN `jos_bl_teams` AS `t1` ON (`m`.`team1_id` = `t1`.`id`) 
          LEFT JOIN `jos_bl_teams` AS `t2` ON (`m`.`team2_id` = `t2`.`id`) 
          LEFT JOIN `jos_vuvuzelaodds_odds` AS `o1` ON (`o1`.`m_id` = `m`.`id`) AND `o1`.`market_id` = 1 AND `o1`.`bookmaker_id` = 1 
          LEFT JOIN `jos_vuvuzelaodds_odds` AS `o2` ON (`o2`.`m_id` = `m`.`id`) AND `o2`.`market_id` = 1 AND `o2`.`bookmaker_id` = 2 
          LEFT JOIN `jos_cwtags_tags` AS `cw1` ON (`cw1`.`item_id` = `o1`.`m_id`) 
          LEFT JOIN `jos_cwtags_tags` AS `cw2` ON (`cw2`.`item_id` = `o2`.`m_id`) 
          WHERE `m`.`published` = 1 AND `s`.`published` = '1' AND `tr`.`published` = '1' 
          AND `m`.`m_played` = '1' 
          AND `o1`.`odds1` != '' AND `o2`.`odds2` != ''
  

Затем запросите его с помощью:

 select * from allTheGames 
  WHERE season_id = 869 AND m_date > 2013-01-01  AND tid1 = 19 AND tid2 = 21 
  

Шаги по оптимизации:

Выясните, какие именно данные вы хотите извлечь из этого запроса и почему: это пользовательский отчет? Веб-страница? вам нужно иметь все эти данные сразу или было бы разумнее, чтобы пользователь детализировал? Как часто выполняется запрос? Раз в минуту? Один раз в день? Сколько записей в каждой таблице? Ваше представление должно отражать этот «игровой объект»

Ваша база данных:

• Запустите «объяснение» для этого запроса http://dev.mysql.com/doc/refman/5.7/en/explain.html .
• Он покажет всю работу, выполняемую базой данных, план выполнения запроса и количество записей, которые он просматривает для этого. Обычно это происходит быстро: фактически запрос не выполняется.
• Посмотрите, можете ли вы поместить базу данных на SSD-диски или даже в оперативную память

Ваша структура таблицы:

• Убедитесь, что у вас есть индексы для всех полей, по которым вы выполняете поиск. Есть много способов оптимизировать это.
• Используйте «explain», чтобы убедиться, что MySQL может использовать индексы.
• Если в таблице jos_vuvuzelaodds_odds действительно только 2 рынка, рассмотрите возможность создания 2 полей.

Удачи!