Есть на сайте телевизионная программа, разбитая по каналам. Необходимо получать название текущей передачи. Формат таблицы с программой такой:

id: bigint
channel_id: int
genre_id: int
date: date
time: time
name: varchar(255)

Как наиболее оптимально получить текущую передачу, то есть передачу, начало которой ближе всего к текущему времени, но раньше, и начало не более 6 часов назад. У меня получилось что-то как-то не очень оптимально:

$sql1 = <<<EOD
SELECT *
FROM `{$this->prefix}programs`
WHERE
channel_id = {$channel_id} AND
(CONCAT_WS(' ', DATE_FORMAT(`date`, '%Y-%m-%d'), TIME_FORMAT(`time`, '%H:%i')) < NOW()) AND
(CONCAT_WS(' ', DATE_FORMAT(`date`, '%Y-%m-%d'), TIME_FORMAT(`time`, '%H:%i')) > NOW() - INTERVAL 6 HOUR)
ORDER BY
`date` DESC, `time` DESC
LIMIT 1
EOD;

Как бы это оптимизировать? Основная проблема - в объединении date и time в datetime-формат, но я не нашел как это сделать по-простому в MySQL.

Да, есть, но в том-то и фишка, что в остальных случаях мне удобнее использовать эти поля поотдельности.

date: date
time: time
dt: datetime

а так нельзя?
интересно в каких это случаях удобнее, всегда есть DATE_FORMAT("%H:%i")

Блин, ну какая разница? Я привел пример того как спроектирована база и спрашиваю как сделать то-то и то-то. Вы говорите: перепроектируй базу и будет проще. Я конечно могу, но это геморройно, т.к. уже написано много кода, который работает именно таким образом. И я уверен, что способ объединить два этих поля в одно - есть, всмысле есть более оптимальный чем мой. А хранить три поля - это как раз не оптимально, нормализованная база данных не должна содержать дублирующейся избыточной информации.

Paul Yanchenko, нормализация баз - это теория. и очень часто, особенно это касается веба, приходится отступать от теории и денормализовывать стуктуру. в вебе важен не объем, занимаемый базой, в конце концов посетителю все равно сколько занимает твой сайт, да и в настоящее время стоимость единицы объема копейки, и продолжает падать. структуру уже спроектированной базы тяжело менять, но иногда приходится. да, не приятно, ну а что делать. у меня лично при проектировании несколько иной подход (уж не знаю на сколько он не "правильный", но лично меня всегда устраивал и я ни разу не сталкивался с проблемами, тем более, что у него есть свои определенные плюсы, впрочем как и минусы). а подход заключается в следующем - использовать как можно меньше специфических типов данных, таких как date, time или datetime. вполне хватает int для хранения timestamp, возвращаемого пхп. мне кажется, что не разумно перекладывать на базу бизнес-логику. база - хранилище информации, не более того. хотя бывают конечно исключения из такого подхода. когда я писал биржежую прогу, то разумнее было перенести большую часть логики в базу, но это только поскольку база поддерживала хранимые процедуру, а каждый раз перекомпилировать модуль, потом выкладывать и переристрировать сом было накладно.
можно конечно провести сранительное тестирование различных подходов, я даже наверняка знаю в чью пользу будут тесты, но мне кажется, что это не тот уровень оптимизации, который нужно проводить в первую очередь.
да, я конечно не ответил на поставленный вопрос, но на будущее все же подумай.

Long, Paul Yanchenko, могу еще добавить, что есть один подход при проектировании таблиц в реляционных БД, называемый dimensional tables, там тоже отсутствует нормализация, за счет чего добивается высокая скорость выполнения запросов и более логичная структура таблиц и их связей.

Long писал(а):

Paul Yanchenko, нормализация баз - это теория. и очень часто, особенно это касается веба, приходится отступать от теории и денормализовывать стуктуру.

У меня обратная ситуация - у меня всегда получалось работать с нормализованной базой.

Long писал(а):

в вебе важен не объем, занимаемый базой, в конце концов посетителю все равно сколько занимает твой сайт, да и в настоящее время стоимость единицы объема копейки, и продолжает падать.

То есть ты хочешь сказать, что раз не видно разницы, то можно делать неправильно?

Long писал(а):

использовать как можно меньше специфических типов данных, таких как date, time или datetime. вполне хватает int для хранения timestamp, возвращаемого пхп. мне кажется, что не разумно перекладывать на базу бизнес-логику. база - хранилище информации, не более того.

Ты прости меня за то что я сейчас скажу, но это бред. Логику НУЖНО переносить в базу данных на столько на сколько это позволяет СУБД. Это даже из названия следует: Система Управления Базами Данных, когда ты переносишь логику в СУБД, последняя может лучше оптимизировать свою работу и делать это более корректно.

Если у тебя дата, то почему ты ее хранишь как integer? Когда тебе понадобится получить unix timestamp, то сделаешь в select'е соответствующее преобразование. И по логике вещей тебе чаще придется преобразовывать int в datetime, чем наоборот, когда дело будет касаться например дней недели, периодов и пр.

Кстати, последние версии MySQL я слышал уже поддерживают и сохраненные процедуры.

Paul Yanchenko, что есть не правильно в твоем понимании? если ты считаешь, что если делать все по теории, то это будет правильно, то ты глубоко ошибаешься. как пример - есть замечательная штука - ORM (признаюсь, узнал о ней совсем-совсем не давно к своему стыду). на базе этого подхода можно легко строить достаточно мощные системы. самое главное - быстро строить и развертывать. но это не значит, что такая система будет работать отимально быстро. даже скорее всего она будет работат медленнее, чем система, написанная при обычном подходе. любая серьезная программа балансирует между двух крайностей - объем кода-данных и скорость работы. практически всегда, если нам необходимо увеличение производительности приходится жертвовать физическим объемом. при существующей сейчас стоимости единицы физического объема выбор делается в пользу производительности, плюс для веба производительность является наиболее критичным параметром. поэтому при проектировании структуры данных просто необходимо таблицы подвергать денормализации.
теперь про бизнес-логику. всегда логически выделяется слой, на котором располагается бизнес-логика приложения. конечно можно на физическом уровне разнести слой с бизнес-логикой на несколько уровней - часть в БД, часть в код. однако, если у нас имеется база, которая поддерживает хранимые процедуры, несомненно лучшим решением будет вынести всю бизнес-логику в базу, а код будет только формировать внешнее представление данных. если же база не поддерживает хранимые процедуры (а муська, которая будет иметь возможность работы с хранимыми процедурами версии 5.0, находится в стадии слабой разработки, сейчас активно разрабатывается только 4я версия - подробнее советую почитать в 3ем номере PHPInsite), итак, если у нас нет хранимых процедур то переносить в базу какую-то бизнес-логику крайне неразумно. объясню почему - бизнес-логика по определению может меняться. соответственно, в случае хранения ее части в БД, необходимо будет менять СТУКТУРУ БД. а стуктура у нас относится к другому логическому уровню. видишь нарушение в логике разработки? есть еще одна маленькая, не столь существенная деталь - специфические типы данных в различных базах реализованы различным образом, да что там, функции по большому счету могут называться по разному. а если заказчик завтра захочет перейти с муськи на другую базу? в случае если ты используешь минимальное количество не стандартных типов проблем не возникнет.
теперь про СУБД. первое. не надо надеятся на то, что СУБД лучше тебя знает что ты от нее хочешь. именно поэтому приходится колдовать с теми же индексами. второе не надо ставить знак равенства между "управлением" и "работой".

да, еще про timestamp. это не нечно абстрактное, как тип datetime. это всего лишь количество секунд прошедших с 1 января 1970 года. поэтому для того чтобы построить любой интервал необходимо воспользоваться простейшими операциями сложения, вычитания и умножения. ну может иногда деления. мне кажется нет необходимости доказывать, что операции с целыми числами выполняются на столько быстро, на сколько возможно а перевод в другой формат необходим только при визуализации данных.

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

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

Изначально мой вопрос стоял как объединить два типа данных в один, что логически вполне осуществимо. Поскольку за время существования топика никто не сказал ничего по существу вопроса, я делаю вывод, что либо это действительно нельзя, либо просто здесь никто не знает как. Пока что я склоняюсь ко второму, поэтому попробую попытать счастья в другом форуме.

Что касается, datetime, то я думаю он гораздо более оптимизирован для работы с датой, чем integer. Попробуй например сделать выборку записей, которые приходятся на вторник первой недели каждого месяца. Без преобразования FROM_UNIXTIME(unixtime_field) тебе не обойтись. Я не знаю как хорошо реализована оптимизация при работе с индексами по полю с типом datetime в MySQL, но мне и не нужно об этом знать - я делаю стандартно, то есть правильно с точки зрения разработчиков MySQL, поэтому в MySQL может быть реализована (уже или потом) какая-то дополнительная оптимизация, которая позволит выполнять специфические для датывремени операции быстрее, чем те же, но с преобразованием из unixtime.

Ты ведь не хранишь например флаги, к которым наиболее применим тип SET, в виде INTEGER чтобы обращаться к ним, используя двоичную маску? Или тоже хранишь? Если да, то совершенно зря, MySQL сделает это быстрее, чем твой PHP-скрипт или если будет парсить сложный SQL-запрос, содержащий математические функции. Я считаю, что нужно позволить MySQL делать то, что она умеет делать хорошо, а самому не заморачиваться об этом.

Paul Yanchenko, я говорю не про то, что надо хранить дату, время и сочетание одновременно. я говорю, что для большинства задач подойтет простой тип int. заметь в самом первом своем посте я сказал, что не предлагаю решения для данной конкретной задачи. я лишь говорю об общем подходе.
а программы ничего не умеют делать. тем более хорошо. поэтому необходимо прикладывать усилия в любом случае, а не перекладывать это на кого-то.

Paul Yanchenko возможно уже не актуально, и незнаю насколько быстрее:
SELECT prg.*,
MIN((EXTRACT(HOUR FROM NOW())*60+EXTRACT(MINUTE FROM NOW())-EXTRACT(HOUR FROM start_time)*60+EXTRACT(MINUTE FROM start_time) ) ) as total_minutes
FROM `prg`
WHERE (EXTRACT(HOUR FROM NOW())*60+EXTRACT(MINUTE FROM NOW())-EXTRACT(HOUR FROM start_time)*60+EXTRACT(MINUTE FROM start_time) ) > 0
GROUP BY prg.channel_id
ORDER BY total_minutes ASC;

P.S. текущую дату не проверяет, но это при желании можно добавить.

Raziel[SD] писал(а):

незнаю насколько быстрее

Если учесть, что здесь вероятен full table scan, то произносить слово "быстрее" я бы поостерегся.

Crazy не совсем внимательно прочитал задачу, действительно можно проще и быстрее

Мне тут сообщили, что если в WHERE запроса, используются функции, применяемые к полям таблицы, то индексы просто отключаются и используется full scan. Нужно стараться избегать применения функций к полям таблицы при поиске.

Paul Yanchenko, хм, вполне логично. только скорее не отключаются, а не используются.
зы. еще один аргумент выносить логику за пределы бд и использовать int

Ну сократить, конечно, можно для наглядности:
SELECT *
FROM `{$this->prefix}programs`
WHERE channel_id = {$channel_id} AND
(CONCAT(`date`, ' ', `time`) BETWEEN (NOW() - INTERVAL 6 HOUR) AND NOW())
ORDER BY `date` DESC, `time` DESC
LIMIT 1

Но без фулл-скана ИМО не обойтись, потому что временные интервалы вычислять надо полюбому.