Оптимизируйте этот SQL-запрос

Этот SQL-запрос вызывает у меня отвращение. Я этого не писал, но это серьезная причина проблем на наших серверах. Я готов разделить его на несколько запросов и выполнить некоторую обработку с помощью PHP (например, RAND()).

$sql = "SELECT a.code, a.ad_id, a.position, a.type, a.image, a.url, a.height, a.width
    FROM " . AD_TABLE ." a, " . USER_GROUP_TABLE . " g
    WHERE (a.max_views >= a.views OR a.max_views = '0')
    AND (FIND_IN_SET(" .$forum_id. ", a.show_forums) > 0 OR a.show_all_forums = '1')
    AND g.user_id = " . $user->data['user_id'] . "
    AND FIND_IN_SET(g.group_id, a.groups)
    AND FIND_IN_SET(" . $user->data['user_rank'] . ", a.ranks)
    AND a.start_time < " . time() . "
    AND a.end_time > " . time() . "
    AND (a.clicks <= a.max_clicks OR a.max_clicks = '0')
    ORDER BY rand()";

Да, я чувствую себя отвратительно после вставки этого...

РЕДАКТИРОВАТЬ: Ниже приведены результаты "ОБЪЯСНЕНИЯ" по образцу запроса в приведенном выше формате, разделенные запятыми:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","g","ref","user_id","user_id","3","const",6,"Using temporary; Using filesort"
1,"SIMPLE","a","ALL","max_views","","","",10,"Using where"

Это

Author: Eric Martindale, 2009-06-30
Source

4 answers

Здесь у вас есть три основные проблемы:

  1. FIND_IN_SET.

    Это невозможно, индекс не может сделать это быстрее. Создайте таблицу отношений "многие ко многим" (или таблицы).

    • a.start_time < GETDATE() AND a.end_time > GETDATE()

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

    • ORDER BY RAND()

    Если вы используете это для выборки данных (т.е. вам не нужно все строки, а скорее небольшое случайное подмножество), есть более эффективный способ сделать это, описанный в этой статье в моем блоге:

 6
Author: Quassnoi, 2009-06-30 14:41:11
  • У вас нет явного соединения в этом запросе между таблицей a и таблицей g: они связаны только с помощью find_in_set (g.group_id, a.группы)
  • Насколько велик ваш набор в "a.группы", т.Е. Содержит ли строка csv в большинстве случаев один идентификатор группы?
  • Если 99% случаев содержат только 1 группу, то сделайте цикл foreach над "a.groups" в php и выполните реальное объединение (или, возможно, это может полностью исключить вашу таблицу user_group_table) из запрос.
    • В меньшинстве случаев, когда у вас более 1 члена группы, запрос все равно будет выполняться нормально с явным присоединением.

Это добавит еще немного кода в ваш класс/функцию php.

 4
Author: blispr, 2009-06-30 14:50:44

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

AND a.start_time < " . time() . "
AND a.end_time > " . time() . "

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

 1
Author: northpole, 2009-06-30 14:31:19

Если вы часто выполняете это.. попробуйте использовать переменные привязки (вместо объединения строк).. чтобы запрос не нужно было анализировать каждый раз..

Редактировать: Извините.. не видел тег MYSQL. Если это не изменилось в последнее время, я не думаю, что MySQL любит подготовленные заявления. ОРАКУЛ, с другой стороны, ЛЮБИТ их.

 0
Author: ShoeLace, 2010-07-08 19:10:45