Использование индекса при сортировке и условии на период

[syfisher]

Использование индекса при сортировке и условии на период

Вопрос про индексы. Вернее, хочу проверить, все ли я правильно понял.

Есть задача - выводить последние пять up-to-date новостей.

Структура таблицы:

CREATE TABLE `news_item` (                                
             `id` bigint(20) NOT NULL auto_increment,                
             `annotation` text,                                      
             `content` longtext,                                     
             `news_date` int(11) default NULL,                       
             `date_start` int(11) default NULL,                      
             `date_end` int(11) default NULL,                        
             PRIMARY KEY  (`id`),                                    
             KEY `node_id` (`node_id`),                              
             KEY `date` (`date_start`,`date_end`,`news_date`)  
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8

DB - MySQl 4.1 WinXp

У меня есть запрос:

SELECT news_item.*  FROM news_item 
WHERE news_item.date_start <= 1170409732 
AND news_item.date_end >=1170409732
ORDER BY news_date DESC 
LIMIT 0,5

где 1170409732 - timestamp текущего времени.

Индекс date при таком запросе не используется. Если поле news_date в индексе поставить первым - используется, но не помогает в ограничении размера выборки, идет сканирование всей таблицы.

Первоначальный индекс используется, если иметь явные сравнения с date_start и date_end, но мне нужно выбирать только up-to-date новости.

В общем-то мотивы, по которым MYSQL отказывается использовать индекс, мне понятны.

В итоге - мне придется применить условие news_item.date_start <=1170409732 AND news_item.date_end >=1170409732 - отдельно:
1) или отдельным запросом(подзапросом)
2) или создать поле is_up_to_date и заполнять его вне основного приложения, например, раз в день по cron

 

[nail]

А не возникала идея заюзать spacial index?
период времени - это отрезок
текущее время - это точка

операция по определению принадлежит ли точка отрезку вроде может использовать индекс

 

[Апельсин]

А почему он должен использоваться?
Даже если абстрагироваться от того что в структуре твоей нет столбца is_published и date_start/date_end которые INT ты сравниваешь либо с несуществующей функцией time(), которая судя по всему должна возвращать значение типа time а не int. У тебя есть допустим такие значения для индекса на столбцы (col1, col2, col3):

(1,1,1)
(1,2,1)
(1,2,3)
(2,3,1)
(2,3,4)
(2,3,7)

как ты себе представляешь использование индекса для сортировки на третий столбец при условии col1>=1 и col2>=2 ?

У тебя после условий where результатом будет:
(1,2,1)
(1,2,3)
(2,3,1)
(2,3,4)
(2,3,7)

 

[syfisher]

Автор оригинала: Апельсин
Даже если абстрагироваться от того что в структуре твоей нет столбца is_published и date_start/date_end которые INT ты сравниваешь либо с несуществующей функцией time(), которая судя по всему должна возвращать значение типа time а не int.

Был немного невнимательным. На самом деле такие поля есть и time() - значение php time(). Щас поправлю сообщение, чтобы не смущать остальных.

Автор оригинала: Апельсин
как ты себе представляешь использование индекса для сортировки на третий столбец при условии col1>=1 и col2>=2 ?

У тебя после условий where результатом будет:
(1,2,1)
(1,2,3)
(2,3,1)
(2,3,4)
(2,3,7)

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

-~{}~ 02.02.07 14:31:

Автор оригинала: nail
А не возникала идея заюзать spacial index?
период времени - это отрезок
текущее время - это точка

операция по определению принадлежит ли точка отрезку вроде может использовать индекс

Такие индексы доступны в mysql 4.1??

 

[Апельсин]

> Такие индексы доступны в mysql 4.1??

они есть, я правда пока не очень понимаю как из можно использовать. Они не могут быть составными. Т.е. если у тебя есть точка вида (date_start, date_end) где date_start и date_end например в формате unixtimestamp. Но ты не можешь создать составной индекс вида (point, non_spatial_column).

 

[syfisher]

Апельсин
Ясно, спасибо.

Решил ввести поле is_up_to_date. Проблемы решились.