Оптимизируйте SQL, который использует между пунктом

что-то вроде этого?

SELECT A.id, B.id 
FROM A
JOIN B ON A.id =  B.id 
WHERE A.event_time BETWEEN B.start_time AND B.end_time

Лично, если бы Вы havea связь "один ко многим" и каждая запись в таблице, единственное касается одной записи в таблице b, я хранил бы идентификатор таблицы b в таблице и затем сделал бы регулярное соединение для получения данных. То, что Вы в настоящее время имеете, является плохим дизайном, который никогда не может быть действительно эффективным.

Я не могу думать о причине для Вас иметь таблицу с 130 000 строк с временными интервалами. Так или иначе должно быть серьезное основание для такого дизайна, и если так, необходимо постараться не пытаться вычислить такое соединение каждый раз. Таким образом, вот мое предложение. Я добавил бы ссылку на B.id в таблице A (B_ID) и триггеры использования для поддержания непротиворечивости. Каждый раз, когда Вы добавляете, новая запись (вставьте триггер) или even_time изменения столбца (триггер обновления), Вы повторно вычислили бы ссылку на B, который на этот раз соответствует. Ваш избранный оператор был бы уменьшен до единственного выбора * от A.

MySQL не позволяет Вам для использования INDEX ORDER BY WITH RANGE в полученных запросах.

Вот почему необходимо будет создать определяемую пользователем функцию.

Обратите внимание, что, если Ваши диапазоны действительно накладываются, запрос только выберет один (который запустился в последний раз).

CREATE UNIQUE INDEX ux_b_start ON b (start_date);

CREATE FUNCTION `fn_get_last_b`(event_date TIMESTAMP) RETURNS int(11)
BEGIN
  DECLARE id INT;
  SELECT b.id
  INTO id
  FROM b
  FORCE INDEX (ux_b_start)
  WHERE b.start_time <= event_date
  ORDER BY
    b.start_time DESC
  LIMIT 1;
  RETURN id;
END;

SELECT COUNT(*) FROM a;

1000


SELECT COUNT(*) FROM b;

200000

SELECT *
FROM (
  SELECT fn_get_last_b(a.event_time) AS bid,
         a.*
  FROM a
) ao, b FORCE INDEX (PRIMARY)
WHERE b.id = ao.bid
  AND b.end_time >= ao.event_time

1000 rows fetched in 0,0143s (0,1279s)

Существует два протеста к моему решению:

1) Вы сказали, что можно добавить индексы, но не изменить схему, таким образом, я не уверен, работало ли это на Вас или не, поскольку у Вас не может быть основанных на функции индексов в MySQL, и необходимо было бы создать дополнительный столбец на Таблице B. 2), другой протест к этому решению состоит в том, что необходимо использовать механизм MyISAM для Таблицы B. Если Вы не можете использовать MyISAM затем эта работа привычки решения, потому что только MyISAM поддерживается для Пространственных Индексов.

Так, предположение, что вышеупомянутые два не являются проблемой для Вас, следующее, должно работать и дать Вам хорошую производительность:

Это решение использует поддержку MySQL Пространственных Данных (см. документацию здесь). В то время как пространственные типы данных могут быть добавлены ко множеству механизмов устройства хранения данных, только MyISAM поддерживается для Пространственных R-древовидных-индексов (см. документацию здесь), которые необходимы для нужданий в производительности. Еще одно ограничение - то, что пространственные типы данных только работают с числовыми данными, таким образом, Вы не можете использовать эту технику с основанными на строке запросами диапазона.

Я привычка вдается в подробности теории позади, как работают пространственные типы и как пространственный индекс полезен, но необходимо посмотреть на объяснение Jeremy Cole здесь в отношении того, как использовать пространственные типы данных и индексы для поисков GeoIP. Также посмотрите на комментарии, поскольку они поднимают некоторые полезные вопросы и альтернативу, если Вы нуждаетесь в необработанной производительности и можете бросить некоторую точность.

Основная предпосылка - то, что мы можем взять запущение/заканчивание и использовать двух из них, чтобы создать четыре отличных точки, один для каждого угла прямоугольника, центрируемого приблизительно 0,0 на xy сетке, и затем сделать быстрый поиск в пространственный индекс, чтобы определить, ли конкретный момент времени, о котором мы заботимся, в прямоугольнике или нет. Как упомянуто ранее, посмотрите объяснение Jeremy Cole более полного обзора того, как это работает.

В Вашем особом случае мы должны будем сделать следующее:

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

alter table B engine = MyISAM;

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

alter table B add column time_poly polygon NOT NULL;

3) Затем мы заполняем новый столбец с данными (имейте в виду, что любые процессы, которые обновляют или вставляют в таблицу B, должны будут быть изменены, чтобы удостовериться, что они заполняют новый столбец также). Так как запуск и диапазоны конца являются временами, мы должны будем преобразовать их в числа с функцией unix_timestamp (см. документацию здесь для того, как это работает).

update B set time_poly := LINESTRINGFROMWKB(LINESTRING(
    POINT(unix_timestamp(start_time), -1),
    POINT(unix_timestamp(end_time), -1),
    POINT(unix_timestamp(end_time), 1),
    POINT(unix_timestamp(start_time), 1),
    POINT(unix_timestamp(start_time), -1)
  ));

4) Затем мы добавляем пространственный индекс к таблице (как упомянуто ранее, это будет только работать на таблицу MyISAM и произведет ошибку "ОШИБКА 1464 (HY000): используемый тип таблицы не поддерживает ПРОСТРАНСТВЕННЫЕ индексы").

alter table B add SPATIAL KEY `IXs_time_poly` (`time_poly`);

5) Затем необходимо будет использовать следующий выбор для использования пространственного индекса при запросах данных.

SELECT A.id, B.id 
FROM A inner join B force index (IXs_time_poly)
ON MBRCONTAINS(B.time_poly, POINTFROMWKB(POINT(unix_timestamp(A.event_time), 0)));

Индекс силы там для создания 100% уверенными, что MySQL будет использовать индекс для поиска. Если бы все подходило, выполняя объяснение на вышеупомянутом выборе, то должен показать что-то подобное следующему:

mysql> explain SELECT A.id, B.id
    -> FROM A inner join B force index (IXs_time_poly)
    -> on MBRCONTAINS(B.time_poly, POINTFROMWKB(POINT(unix_timestamp(A.event_time), 0)));
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                                           |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |    1065 |                                                 | 
|  1 | SIMPLE      | B     | ALL  | IXs_time_poly | NULL | NULL    | NULL | 7969897 | Range checked for each record (index map: 0x10) | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
2 rows in set (0.00 sec)

См. анализ Jeremy Cole для получения дополнительной информации о выигрышах в производительности этого метода по сравнению с между пунктом.

Сообщите мне, если у Вас возникнут вопросы.

Спасибо,

- Dipin

Поместите индекс на убывание B.start_time и затем используйте этот запрос:

 SELECT A.id AS idA,
 (SELECT B.id FROM B WHERE A.event_time > B.start_time LIMIT 0, 1
 ORDER BY B.start_time DESC) AS idB
 FROM A

Поскольку блоки времени в B являются непересекающимися, это дало бы Вам первый блок времени соответствия und, Вы избавляетесь от между, но все еще наличие подзапроса там. Возможно, включая B.id в индексе дал бы Вам некоторое дополнительное маленькое повышение производительности. (правовая оговорка: не уверенный в синтаксисе MySQL)

На основе Вашего комментария, что каждая запись в A соответствует точно одной записи в B, самое легкое решение состояло бы в том, чтобы удалить AUTOINCREMENT из идентификационного столбца B затем замените все идентификаторы B с идентификаторами от A.

Daremon, этот ответ основан на одном из Ваших комментариев, где Вы сказали что каждая запись в таблице карты только к одной записи в таблице B,

Можно ли добавить дополнительную таблицу к схеме? Если да, можно предварительно вычислить результат этого запроса и сохранить его в другой таблице. Необходимо будет также сохранить эту предварительно вычисленную таблицу в синхронизации с изменениями в таблицах A и B

Дайте попытку с помощью стандартного оператора сравнения (<и>).

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

Заботьтесь для помещения в индекс Вашего A.event_time и затем помещенный в другой индекс B.start_time и B.end_time.

Если, поскольку Вы сказали, что это - единственное одно условие, которое связывает эти два объекта, я думаю, что это - единственное решение, которое можно взять.

Fede

Не изменяя схему означают, что Вы не можете добавить индекс? Попробуйте много индекс столбца на start_time и end_time.

Если Вы не можете изменить схему - в частности, если Вы не можете включить индекс event_time, я не вижу большого простора для совершенствования на уровне SQL.

Я был бы более склонен сделать это в коде.

• читайте все B начинать/заканчиваться/идентификатор кортежи в список, отсортированный на времени начала
• считайте все события
• для каждого событие
• • найдите самое большое время начала <= временем события (двоичный поиск сделает прекрасный),
• • если время события является <= временем окончания, добавьте к этому списку B событий
• • еще этот B не имеет никакого дома

Я вижу, что Вы делаете перекрестное объединение двух таблиц. Это не очень хорошо, и DBMS займет много времени для выполнения той операции. Перекрестное объединение является большей частью exepensive операции в SQL. Причина такого долгого времени выполнения могла быть этим.

Сделайте на том пути, он мог решить...

ВЫБЕРИТЕ A.id, B.id ОТ A, B ГДЕ A.id = B.id И event_time МЕЖДУ B.start_time И B.end_time

Я надеюсь эта справка Вы :)

Заметьте, что при выполнении этого запроса, Вы на самом деле создаете 980x130000 записи в памяти прежде, чем применить условие. Такое СОЕДИНЕНИЕ не очень рекомендуется, и я вижу, почему оно даст Вам проблемы производительности.

Я обычно не рекомендовал бы запроса как это, но...

Так как Вы указали ту таблицу, единственное имеет приблизительно 980 строк и что каждая строка отображается точно на одну строку в таблице B, затем Вы могли сделать следующее, и это, скорее всего, будет намного быстрее, чем декартово соединение:

SELECT A.id AS id_from_a,
    (
        SELECT B.id
        FROM B
        WHERE A.event_time BETWEEN B.start_time AND B.end_time
        LIMIT 0, 1
    ) AS id_from_b
FROM A

Я не уверен, что это может быть оптимизировано полностью. Я попробовал его на MySQL 5.1.30. Я также включил индекс {B.start_time, B.end_time} как предложили другие люди. Затем я получил отчет от EXPLAIN, но лучшим, который я мог получить, является Метод доступа Диапазона:

EXPLAIN SELECT A.id, B.id FROM A JOIN B 
ON A.event_time BETWEEN B.start_time AND B.end_time;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | A     | ALL  | event_time    | NULL | NULL    | NULL |    8 |                                                | 
|  1 | SIMPLE      | B     | ALL  | start_time    | NULL | NULL    | NULL |   96 | Range checked for each record (index map: 0x4) | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

См. примечание по крайнему правому. Оптимизатор думает, что смог использовать индекс на {B.start_time, B.end_time} но это закончило тем, что решило не использовать тот индекс. Ваши результаты могут варьироваться, потому что Ваше распределение данных является более представительным.

Сравните с индексным использованием, если Вы выдерживаете сравнение A.event_time к постоянному диапазону:

EXPLAIN SELECT A.id FROM A
WHERE A.event_time BETWEEN '2009-02-17 09:00' and '2009-02-17 10:00';

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | A     | range | event_time    | event_time | 8       | NULL |    1 | Using where | 
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

И сравните с зависимой подформой запросов, данной @Luke и @Kibbee, который, кажется, использует индексы эффективнее:

EXPLAIN SELECT A.id AS id_from_a,
    (
        SELECT B.id
        FROM B
        WHERE A.id BETWEEN B.start_time AND B.end_time
        LIMIT 0, 1
    ) AS id_from_b
FROM A;

+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | A     | index | NULL          | PRIMARY | 8       | NULL |    8 | Using index | 
|  2 | DEPENDENT SUBQUERY | B     | ALL   | start_time    | NULL    | NULL    | NULL |  384 | Using where | 
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+

Странно, ОБЪЯСНИТЕ списки possible_keys как ПУСТОЙ УКАЗАТЕЛЬ (т.е. никакие индексы мог использоваться), но затем решает использовать первичный ключ, в конце концов. Могла быть особенность MySQL, ОБЪЯСНЯЕТ отчет?

Существует ли индекс на B (start_time, end_time)? В противном случае, возможно, добавление того могло бы ускорить соответствие строк B к строки?

Обратите внимание, если Вы не можете изменить схему, возможно, Вы не можете создать новые индексы также?