Mysql Использование индекса для WHERE

[WBS]

Есть две большие таблицы:

CREATE TABLE series (
  n INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)

CREATE TABLE data (
  dt_start DATETIME NOT NULL,
  dt_end DATETIME NOT NULL
)

Запрос:

SELECT dt_start, dt_end, n FROM data, series
WHERE n <= DATEDIFF(dt_end, dt_start) + 1

Этот запрос выполняется очень долго. Выражение "DATEDIFF(dt_end, dt_start) + 1" почти всегда равно 1, иногда 2-3. Даже убедившись, что n, равное 3, уже больше "DATEDIFF(dt_end, dt_start) + 1", MySQL перебирает все строки из таблицы series (десятки и сотни тысяч строк), подставляя n=4, n=5 и т.д. Хотя есть индекс, и очевидно, что меньших n в таблице series не осталось.

Если добавить дополнительное условие "n <= 3", то запрос выполнится быстро. Можно ли ускорить выполнение запроса, не устанавливая такое жесткое ограничение?

 

[AnrDaemon]

Судя по вашему посту, у вас получается матрица [data × series] соответствующего размера. Естественно, любой запрос будет медленным, даже если там всего несколько тысяч записей.
Либо давайте нормально входные данные, либо возвращайтесь за кульман и чертите нормальную структуру, прежде чем код писать.

 

[MiksIr]

Матрицу можно уменьшить, если условие перенести в ON.
Мне правда интересно, что за задача такая решается таким стремным запросом.

 

[fixxxer]

Очевидно, нужен индекс на выражение DATEDIFF(dt_end, dt_start) + 1. Варианты:
1) взять нормальную базу, умеющую в функциональные индексы;
2) взять mariadb, сделать persistent virtual column и индекс на него;
3) сделать денормализацию в отдельное поле вручную - триггерами или на уровне приложения;
4) придумать какую-то иную структуру данных.

 

[WBS]

у вас получается матрица [data x series] соответствующего размера

Совершенно верно. И вопрос как раз в том, почему она не хочет уменьшаться с учетом индекса по столбцу n.

Матрицу можно уменьшить, если условие перенести в ON.

Вы имели в виду "... INNER JOIN ... ON ..."?
Это запрос не ускоряет.

Мне правда интересно, что за задача такая решается таким стремным запросом.

Это задача группировки данных по разным интервалам времени (привычным: час, день, месяц; или более необычным: по 12 часов, по 3 дня). Задача группировки данных по часам уже обсуждалась на форуме, Sad Spirit предложил решение для PostgreSQL через generate_series(). В MySQL нет функции generate_series(). Вместо нее для генерации последовательности чисел используются либо переменные (получаются громоздкие сложные запросы), либо заранее созданные таблицы с последовательностями чисел (у меня это таблица series).

Очевидно, нужен индекс на выражение...

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

4) придумать какую-то иную структуру данных.

В рассматриваемом примере таблица с данными и так максимально упрощена (начало события, конец события - все), проще она быть в принципе не может. И на практике она будет сложнее.

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

Я могу сгенерировать таблицу series на 20 записей, и все будет очень быстро.
Я могу вписать условие "n <= 20", и опять все будет быстро.

Вопрос в том, почему на таблице series в 100k записей все медленно?
Почему при связывании таблиц MySQL не использует индекс для отбрасывания лишних строк (так, как он это делает для условия "n <= 20", т.е. при сравнении с константой)?
И можно ли без костылей ускорить выполнение запроса?

 

[hell0w0rd]

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

Чейта не годится. На каждое условие и нужно сделать индекс. А по вашему индексы нужно от балды делать?

Это задача группировки данных по разным интервалам времени (привычным: час, день, месяц; или более необычным: по 12 часов, по 3 дня)

В вышеупомянутой нормальной БД есть тип interval, на пример.

 

[WBS]

Чейта не годится. На каждое условие и нужно сделать индекс.

Как я упоминал, разных условий может быть много (5, 10, 20). Просто в голове не укладывается, что разумно делать 20 индексов на таблицу с 2-мя полями. Допустим, это в порядке вещей (просто я с таким не сталкивался).

И даже забудем о том, что тема про MySQL.

Как дополнительный индекс поможет в сравнении? Индекс на левую часть выражения (на поле n) уже есть, и он не используется так, как хотелось бы. Зачем нам индекс на правую часть?


Я жалею, что пример задачи для форума я не упростил еще сильнее. Исправляюсь...

Создаем и заполняем две таблицы с числами:

$nulls = array_fill(0, 120000, "(NULL)");
$nulls_str = implode(", ", $nulls);

for ($i=1; $i<=2; $i++) {
  $sql = "CREATE TABLE series{$i} (n{$i} INT PRIMARY KEY NOT NULL AUTO_INCREMENT)";
  mysql_query($sql);
  $sql = "INSERT INTO series{$i} VALUES {$nulls_str}";
  mysql_query($sql);
}

Запрос:

SELECT n1, n2 FROM series1, series2 WHERE n1 > n2

EXPLAIN покажет, что происходит полное перемножение таблиц. MySQL не может сообразить, что связывая строку n1=1 с таблицей series2, не нужно просматривать всю таблицу series2. Если (n1=1) не больше (n2=1), то (n1=1) уже будет не больше любого значения из таблицы series2.

Можно ли это как-то исправить, чтобы ускорить выполнение запроса?

 

[AnrDaemon]

Так от тебя зависит, как построить объединение таблиц. По умолчанию да, строится полная матрица. Почитай документацию о том, что на самом деле означает твоё "SELECT … FROM table1, table2".
Программа же не обладает интеллектом, и мыслей не читает. Она делает только то, что в неё заложено. Тобой в том числе.
Могу ещё про зеркало напомнить. Да.

 

[флоппик]

Просто в голове не укладывается, что разумно делать 20 индексов на таблицу с 2-мя полями. Допустим, это в порядке вещей (просто я с таким не сталкивался).

Индексы в принципе нужны не таблице — зачем они ей? Индексы нужны выборкам относительно их критериев. Так лучше укладывается в голове?

 

[fixxxer]

В MySQL нет функции generate_series()

select 1 union all select 2 union all select 3 ...

 

[флоппик]

fixxxer, мсье знает толк...))

 

[MiksIr]

А мне тоже непонятно, зачем тут индекс по выражению.
Конкретно в примере SELECT dt_start, dt_end, n FROM data, series WHERE n <= DATEDIFF(dt_end, dt_start) + 1

 

[fixxxer]

Эмм, BNL join mysql же вроде давно научился, не?

UPD: а, там же <= !!111 slowpoke.jpg

Но тогда я вообще не понимаю задачу, зачем так? Что за задача решается-то?

 

[MiksIr]

Да не, может ты и прав. Другое дело, как я понимаю, block nested loop не то, что бы прям волшебная палочка по производительности.