MySQL присоединиться к подзапросам

Question

У меня есть следующие таблицы:

CREATE TABLE `data` (
  `date_time` decimal(26,6) NOT NULL,
  `channel_id` mediumint(8) unsigned NOT NULL,
  `value` varchar(40) DEFAULT NULL,
  `status` tinyint(3) unsigned DEFAULT NULL,
  `connected` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`channel_id`,`date_time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `channels` (
  `channel_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `channel_name` varchar(40) NOT NULL,
  PRIMARY KEY (`channel_id`),
  UNIQUE KEY `channel_name` (`channel_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Мне было интересно, если кто-нибудь может дать мне несколько советов о том, как оптимизировать или переписать следующий запрос:

SELECT channel_name, t0.date_time, t0.value, t0.status, t0.connected, t1.date_time, t1.value, t1.status, t1.connected FROM channels,
    (SELECT MAX(date_time) AS date_time, channel_id, value, status, connected FROM data
        WHERE date_time <= 1300818330
        GROUP BY channel_id) AS t0
    RIGHT JOIN
    (SELECT MAX(date_time) AS date_time, channel_id, value, status, connected FROM data
        WHERE date_time <= 1300818334
        GROUP BY channel_id) AS t1
ON t0.channel_id = t1.channel_id
WHERE channels.channel_id = t1.channel_id

В основном я получаю значения, статус и подключенные поля для каждого имени канала в два разных времени. Поскольку t0 всегда <= t1, поля могут существовать для t1, но не для t0, и я хочу, чтобы это было показано. Вот почему я использую ПРАВИЛЬНОЕ СОЕДИНЕНИЕ. Если он не существует для t1, то он не будет существовать для t0, поэтому строка не должна возвращаться.

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

Также было бы хорошо иметь возможность добавить логическое поле в каждую из последних строк, что верно, когда t0.value = t1.value & t0.status = t1.status & t0.connected = t1.connected.

Большое спасибо за уделенное время.

Answer 1

Вы можете уменьшить два подзапроса до одного

SELECT channel_id,
   MAX(date_time) AS t1_date_time,
   MAX(case when date_time <= {$p1} then date_time end) AS t0_date_time
FROM data
WHERE date_time <= {$p2}
GROUP BY channel_id

GROUP BY, как известно, вводит в заблуждение в MySQL. Представьте, если бы у вас были MIN() и MAX() в одном и том же select, из какой строки должны исходить не сгруппированные столбцы? Как только вы поймете это, вы поймете, почему это не является детерминированным.

Чтобы получить полные строки t0 и t1

SELECT x.channel_id,
       t0.date_time, t0.value, t0.status, t0.connected,
       t1.date_time, t1.value, t1.status, t1.connected
FROM (
    SELECT channel_id,
       MAX(date_time) AS t1_date_time,
       MAX(case when date_time <= {$p1} then date_time end) AS t0_date_time
    FROM data
    WHERE date_time <= {$p2}
    GROUP BY channel_id
) x
INNER JOIN data t1 on t1.channel_id = x.channel_id and t1.date_time = x.t1_date_time
LEFT JOIN data t0 on t0.channel_id = x.channel_id and t0.date_time = x.t0_date_time

И, наконец, объединение, чтобы получить название канала

SELECT c.channel_name,
       t0.date_time, t0.value, t0.status, t0.connected,
       t1.date_time, t1.value, t1.status, t1.connected,
       t0.value=t1.value AND t1.status=t0.status
                         AND t0.connected=t1.connected name_me
FROM (
    SELECT channel_id,
       MAX(date_time) AS t1_date_time,
       MAX(case when date_time <= {$p1} then date_time end) AS t0_date_time
    FROM data
    WHERE date_time <= {$p2}
    GROUP BY channel_id
) x
INNER JOIN channels c on c.channel_id = x.channel_id
INNER JOIN data t1 on t1.channel_id = x.channel_id and t1.date_time = x.t1_date_time
LEFT JOIN data t0 on t0.channel_id = x.channel_id and t0.date_time = x.t0_date_time

---

РЕДАКТИРОВАТЬ

Чтобы выполнить RLIKE для имени канала, достаточно просто добавить предложение WHERE в конце запроса на c.channel_name, Однако может быть лучше выполнить фильтрацию в подзапросе, используя функцию MySQL для обработки соединений через запятую слева направо.

SELECT x.channel_name,
       t0.date_time, t0.value, t0.status, t0.connected,
       t1.date_time, t1.value, t1.status, t1.connected,
       t0.value=t1.value AND t1.status=t0.status
                         AND t0.connected=t1.connected name_me
(
    SELECT c.channel_id, c.channel_name,
       MAX(d.date_time) AS t1_date_time,
       MAX(case when d.date_time <= {$p1} then d.date_time end) AS t0_date_time
    FROM channels c, data d
    WHERE c.channel_name RLIKE {$expr}
      AND c.channel_id = d.channel_id
      AND d.date_time <= {$p2}
    GROUP BY c.channel_id
) x
INNER JOIN data t1 on t1.channel_id = x.channel_id and t1.date_time = x.t1_date_time
LEFT JOIN data t0 on t0.channel_id = x.channel_id and t0.date_time = x.t0_date_time