Является ли этот запрос неприводимо сложным?

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

SELECT d.id, d.name, l.message
FROM device AS d
LEFT JOIN (
  SELECT l1.device_id, l1.message
  FROM log AS l1
  WHERE l1.when = (
        SELECT MAX(l2.when)
        FROM log AS l2
        WHERE l2.device_id = l1.device_id
  ) l ON l.device_id = d.id
WHERE d.active = 1
ORDER BY d.id ASC;

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

Вот альтернатива, для которой требуется только один экземпляр таблицы журнала:

SELECT    d.id, d.name, 
          SUBSTRING_INDEX(
              GROUP_CONCAT(
                  l.message 
                  SEPARATOR '~' 
                  ORDER BY l.when DESC
              ) 
          ,   '~'
          ,   1
          )
FROM      device d
LEFT JOIN log    l
ON        d.id = l.device_id
WHERE     d.active = 1
GROUP BY  d.id

Этот запрос находит последнее сообщение журнала, создавая список сообщений, разделенных тильдой, отсортированных по дате в порядке убывания. Это сделано GROUP_CONCAT. Чипы SUBSTRING_INDEX первой записи этого списка.

У этого подхода есть 2 недостатка:

• он использует GROUP_CONCAT. Если результат этой функции становится слишком длинным, он усекается. Вы можете исправить это, если сделаете

  SET @@group_concat_max_len = @@max_allowed_packet;

перед выполнением запроса. Вы можете сделать даже лучше: поскольку вы заинтересованы в получении только одного сообщения, вы можете установить group_concat_max_len таким же большим, как максимальная длина символов столбца message. Это значительно сэкономит память по сравнению с использованием @@max_alowed_packet.

• он опирается на специальный разделитель (в примере это тильда ('~')), который не должен появляться в тексте сообщения. Вы можете изменить это на любую строку разделителя, если вы уверены, что она не появляется внутри текста сообщения.

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

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

SELECT    d.id
,         d.name
,         l.message
FROM      (
          SELECT    d.id, d.name, MAX(l.when) lmax
          FROM      device d
          LEFT JOIN log    l
          ON        d.id = l.device_id
          WHERE     d.active  = 1
          GROUP BY  d.id
          ) d
LEFT JOIN log       l
ON        d.id   = l.device_id
AND       d.lmax = l.when
ORDER BY d.id ASC;

другая альтернатива:

SELECT    d.id
,         d.name
,         l2.message
FROM      device d
LEFT JOIN (
          SELECT   l.device_id
          ,        MAX(l.when) lmax
          FROM     log l
          GROUP BY l.device_id
          ) l1
ON        d.id = l1.device_id 
LEFT JOIN log       l2
ON        l1.device_id = l2.device_id
AND       l1.lmax      = l2.when
WHERE     d.active     = 1
ORDER BY  d.id ASC;

Ваш запрос и приведенные ниже стратегии выиграют от индекса ON log(device_id,when). Этот индекс может заменить индекс ON log(device_id), так как этот индекс будет избыточным.

Если у вас есть целая куча записей журнала для каждого устройства, JOIN в вашем запросе создаст промежуточный набор результатов хорошего размера, который будет отфильтрован до одной строки для каждого устройства. Я не верю, что у оптимизатора MySQL есть какие-либо «ярлыки» для этой операции антисоединения (по крайней мере, не в 5.1)... но ваш запрос может быть наиболее эффективным.

Вопрос. Могу ли я выполнить работу, используя другую стратегию?

Да, есть и другие стратегии, но я не уверен, что какая-либо из них «лучше», чем ваш запрос.

ОБНОВЛЕНИЕ:

Одной из стратегий, которую вы можете рассмотреть, является добавление в вашу схему еще одной таблицы, содержащей самую последнюю запись журнала для каждого устройства. Это может поддерживаться с помощью TRIGGER, определенных в таблице log. Если вы выполняете только вставки (без ОБНОВЛЕНИЯ и УДАЛЕНИЯ самой последней записи журнала, это довольно просто. Всякий раз, когда выполняется вставка в таблицу log, запускается триггер AFTER INSERT FOR EACH ROW, который сравнивает значение when, вставляемое в журнал. таблицы для device_id, к текущему значению when в таблице log_latest, и вставляет/обновляет строку в таблице log_latest, чтобы всегда была самая последняя строка. Вы также можете (избыточно) сохранить имя устройства в таблице. ( В качестве альтернативы вы можете добавить столбцы latest_when и latest_message в таблицу устройств и сохранить их там.)

Но эта стратегия выходит за рамки вашего первоначального вопроса ... но это работоспособная стратегия, которую следует учитывать, если вам нужно часто запускать запрос «последнее сообщение журнала для всех устройств». Недостатком является то, что у вас есть дополнительная таблица и снижение производительности при выполнении вставок в таблицу log. Эта таблица может быть полностью обновлена ​​с помощью запроса, аналогичного исходному, или приведенных ниже альтернатив.

Один из подходов — это запрос, который выполняет простое соединение таблиц device и log, получает строки, упорядоченные по устройствам и по убыванию when. Затем используйте переменную памяти для обработки строк, чтобы отфильтровать все, кроме «последней» записи журнала. Обратите внимание, что этот запрос возвращает дополнительный столбец. (Этот дополнительный столбец можно удалить, обернув весь запрос как встроенное представление, но вы, скорее всего, получите лучшую производительность, если сможете жить с возвращаемым дополнительным столбцом:

SELECT IF(s.id = @prev_device_id,0,1) AS latest_flag
     , @prev_device_id := s.id AS id
     , s.name
     , s.message
  FROM (SELECT d.id
             , d.name
             , l.message
          FROM device d
          LEFT
          JOIN log l ON l.device_id = d.id
         WHERE d.active = 1
         ORDER BY d.id, l.when DESC
       ) s
  JOIN (SELECT @prev_device_id := NULL) i
HAVING latest_flag = 1

Что делает первое выражение в списке SELECT, так это «отмечает» строку всякий раз, когда значение идентификатора устройства в этой строке ОТЛИЧАЕТСЯ от идентификатора устройства в ПРЕДЫДУЩЕЙ строке. Предложение HAVING отфильтровывает все строки, не отмеченные цифрой 1. (Вы можете опустить предложение HAVING, чтобы увидеть, как работает это выражение.)

(Я не проверял это на синтаксические ошибки, если вы получите сообщение об ошибке, дайте мне знать, и я посмотрю повнимательнее. Моя проверка на столе говорит, что все в порядке... но, возможно, я пропустил скобку или запятую,)

(Вы можете «избавиться» от этого дополнительного столбца, заключив его в другой запрос

SELECT r.id,r.name,r.message FROM (
/* query from above */
) r

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

Конечно, добавьте ORDER BY к самому внешнему запросу, чтобы гарантировать, что ваш набор результатов будет упорядочен так, как вам нужно.

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

ОБНОВЛЕНИЕ:

Если вы получаете практически все строки из device (где предикат не очень избирательный), вы, вероятно, можете повысить производительность, получив последнюю запись журнала для каждого устройства_id в таблице log и отложив присоединение к таблице device. (Но обратите внимание, что индекс для этого промежуточного набора результатов не будет доступен для выполнения соединения, поэтому его действительно необходимо протестировать для оценки производительности.)

SELECT d.id
     , d.name
     , t.message
  FROM device d 
  LEFT
  JOIN (SELECT IF(s.device_id = @prev_device_id,0,1) AS latest_flag
             , @prev_device_id := s.device_id AS device_id
             , s.messsage
          FROM (SELECT l.device_id
                     , l.message
                  FROM log l
                 ORDER BY l.device_id DESC, l.when DESC
               ) s
          JOIN (SELECT @prev_device_id := NULL) i
        HAVING latest_flag = 1
       ) t
    ON t.device_id = d.id

ПРИМЕЧАНИЕ. Мы указываем порядок убывания для столбцов device_id и when в предложении ORDER BY встроенного представления с псевдонимом s не потому, что нам нужны строки в порядке убывания идентификатора устройства, а чтобы избежать файловой сортировки, позволяя MySQL выполнять операцию «обратного сканирования» в индексе с ведущими столбцами (device_id, когда).

ПРИМЕЧАНИЕ. Этот запрос по-прежнему будет буферизовать промежуточный набор результатов как временные таблицы MyISAM, и для них не будет никакого индекса. Так что, скорее всего, это не будет работать так же хорошо, как ваш исходный запрос.

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

SELECT d.id
     , d.name
     , ( SELECT l.message
           FROM log l
          WHERE l.device_id = d.id
          ORDER BY l.when DESC 
          LIMIT 1
       ) AS message
  FROM device d
 WHERE d.active = 1
 ORDER BY d.id ASC;

ПРИМЕЧАНИЕ. Поскольку id является ПЕРВИЧНЫМ КЛЮЧЕМ (или УНИКАЛЬНЫМ КЛЮЧЕМ) в таблице device, и поскольку вы не выполняете никаких операций JOIN, которые будут генерировать дополнительные строки, вы можете опустить предложение GROUP BY.

ПРИМЕЧАНИЕ. В этом запросе будет использоваться операция "вложенные циклы". То есть для каждой строки, возвращаемой из таблицы device, (по существу) необходимо выполнить отдельный запрос, чтобы получить соответствующую строку из журнала. Только для нескольких строк device (которые будут возвращены с более избирательным предикатом для таблицы device) и с кучей записей журнала для каждого устройства производительность будет не так уж плоха. Но для многих устройств, каждое из которых имеет всего несколько сообщений журнала, другие подходы, скорее всего, будут намного более эффективными.)

Также обратите внимание, что при таком подходе вы можете легко расширить его, чтобы также возвращать второе последнее сообщение журнала в виде отдельного столбца, добавив еще один подзапрос (как и первый) в список SELECT, просто изменив предложение LIMIT, чтобы пропустить первую строку и получить вместо нее вторую строку.

     , ( SELECT l.message
           FROM log l
          WHERE l.device_id = d.id
          ORDER BY l.when DESC 
          LIMIT 1,1
       ) AS message_2

Для получения практически всех строк с устройства вы, вероятно, получите наилучшую производительность, используя операции JOIN. Единственным недостатком этого подхода является то, что он потенциально может возвращать несколько строк для устройства, когда есть две (или более) строки, которые имеют совпадающее последнее значение when для устройства. (В принципе, этот подход гарантированно возвращает «правильный» набор результатов, когда у нас есть гарантия, что log(device_id,when) уникален.

С помощью этого запроса в качестве встроенного представления, чтобы получить «последнее» значение когда:

SELECT l.device_id
     , MAX(l.when)
  FROM log l
 GROUP BY l.device_id 

Мы можем присоединить это к таблицам журналов и устройств.

SELECT d.id
     , d.name
     , m.messsage
  FROM device d
  LEFT
  JOIN (
         SELECT l.device_id
              , MAX(l.when) AS `when`
           FROM log l
          GROUP BY l.device_id 
       ) k
    ON k.device_id = d.id
  LEFT
  JOIN log m 
    ON m.device_id = d.id
       AND m.device_id = k.device_id
       AND m.when = k.when
 ORDER BY d.id 

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