Запрос выполняется слишком долго. Оптимизация запроса.

[AndreyKl]

Запрос выполняется слишком долго. Оптимизация запроса.

Здравствуйте, уважаемое сообщество.

Вот возникла проблема. Запрос выполняется слишком долго.
Записей в таблице около 100 000 (судя по динамике, нужно расчитывать примерно на 1-2 миллиона). Время выполнения запроса около 0,4 секунды. Если убрать ORDER BY, то время уменьшается на несколько порядков (оно и ясно...) (0,01 секунды). Большинство обращений - именно к этой таблице. И если поиск (это поисковый запрос) происходит слишком часто, то сайт отвечает медленно . Что предпринять для уменьшения времени выполнения запроса?
MySQL 5.0

Запрос

SELECT 
                 pictures.name,
                 pictures.title,
                 pictures.date, 
                 DATE_FORMAT(pictures.date,'%b %D %Y') as vidDate, 
                 pictures.user, 
                 pictures.id_picture, 
                 pictures.views, 
                 pictures.album 
FROM 
                 pictures 
WHERE 
                 (pictures.title LIKE '%cars%' OR pictures.description LIKE '%cars%') 
                 AND pictures.public=1 AND activ=1 
ORDER BY 
                 date DESC 
LIMIT 0, 40

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

CREATE TABLE `pictures` (
  `id_picture` int(11) NOT NULL auto_increment,
  `name` varchar(31) collate utf8_unicode_ci NOT NULL,
  `title` varchar(63) collate utf8_unicode_ci NOT NULL,
  `description` varchar(510) collate utf8_unicode_ci NOT NULL,
  `user` int(11) NOT NULL,
  `public` tinyint(1) NOT NULL,
  `activ` tinyint(1) NOT NULL default '1',
  `date` datetime NOT NULL,
  `code` mediumblob NOT NULL,
  `album` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  PRIMARY KEY  (`id_picture`),
  KEY `public` (`public`),
  KEY `activ` (`activ`),
  KEY `title` (`title`),
  KEY `name` (`name`),
  KEY `user` (`user`),
  KEY `date` (`date`),
  KEY `album` (`album`),
  KEY `views` (`views`),
  KEY `description` (`description`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97961 ;

 

[voituk]

При любой DESC сортировке создается временная таблица - оттуда и тормоза запроса.

 

[AndreyKl]

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

 

[.des.]

(pictures.title LIKE '%cars%' OR pictures.description LIKE '%cars%')
мешает использованию индекса. Может быть попробуете FULLTEXT INDEX?

А вообще нужен explain

 

[vovik]

voituk
По идее, если есть индекс на сортировочное поле и mysql решит его использовать, то временной таблицы не будет.

Предлагаю попробовать FROM pictures USE INDEX (date).

Не факт что будет быстрее, но попробовать стоит.

 

[AndreyKl]

.des.
Да, но по всей видимости, дело не в этом. Видимо, дело именно в ORDER BY date DESC. Если убрать order by, то запрос возвращает результаты "моментально", даже учитывая, что поиск не полнотекстный и % в начале выражения like.

А вообще нужен explain

Что именно?

-~{}~ 29.08.06 18:54:

vovik
SELECT
pictures.name,
pictures.title,
pictures.date,
DATE_FORMAT(pictures.date,'%b %D %Y') as vidDate,
pictures.user,
pictures.id_picture,
pictures.views,
pictures.album
FROM
pictures
USE INDEX (date)
WHERE
(pictures.title LIKE '%cars%' OR pictures.description LIKE '%cars%')
AND pictures.public=1 AND activ=1
ORDER BY
date DESC
LIMIT 0, 40

время 0,01 !!!

спасибо огромное
vovik

 

[.des.]

explain ваш запрос;
команда такая. http://dev.mysql.com/explain

еще можно попробовать время от времени выполнять --sort-records по индексу date (только в обратном порядке) http://dev.mysql.com/doc/refman/5.0/en/myisamchk-other-options.html
Но это крайняя мера, сначало нужен explain

-~{}~ 29.08.06 17:58:

2 AndreyKl добавьте к вашему запрос SQL_NO_CACHE для чистоты эксперимента

 

[AndreyKl]

.des.
Спасибо, пойду смотреть команду.

-~{}~ 29.08.06 19:02:

.des.

Добавил, результаты те же.

-~{}~ 29.08.06 19:08:

.des.
explain показал
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pictures ref public,activ public 1 const 85298 Using where; Using filesort

после добавления use index исчез Using filesort:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pictures index NULL date 8 NULL 90460 Using where

-~{}~ 29.08.06 19:10:

собственно, вроде бы всё ясно, явное задание индекса заставило mysql не использовать файловую сортировку...
Если всё так, то тема закрыта. Всем спасибо.

 

[Nutty]

стоит попробовать ещё и полнотекстовый поиск, как говорилось выше. а так же прочитать про оптимизацию таблиц в мануале и про оператор explain.