Добрый день!
Поиск работает по типу запроса:
| Код |
SELECT `id`, `name`, `brand`, `price`, `content`, `code`, `article`, `article_index`, `image_url`, `page_url`, `site_group`, `preview_text`, `discontinued`, `badge_type`, `badge_text`, `badge_preview`, `quantity`, IF(`quantity`>0,1,0) available, (
MATCH(`name`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) * 5 +
MATCH(`brand`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) +
MATCH(`code`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) +
MATCH(`article`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) +
MATCH(`content`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) * 2 +
MATCH(`site_group`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) * 6
) AS `relev`
FROM `r_search_products`
WHERE (
MATCH(`name`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) OR
MATCH(`brand`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) OR
MATCH(`code`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) OR
MATCH(`article`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) OR
MATCH(`content`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode) OR
MATCH(`site_group`) AGAINST('>>"pocketbook" >(+pocketbook*) <(pocketbook*)' in boolean mode)
)
ORDER BY `discontinued`, `available` DESC, `relev` DESC;
|
Используется полнотекстовый поиск.
Структура таблицы:
| Код |
CREATE TABLE `r_search_products` (
`id` int(10) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`brand` varchar(50) DEFAULT NULL,
`content` text,
`price` decimal(10,0) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`article` varchar(255) DEFAULT NULL,
`article_index` varchar(255) DEFAULT NULL,
`discontinued` tinyint(1) NOT NULL,
`page_url` varchar(255) NOT NULL,
`image_url` varchar(255) DEFAULT NULL,
`site_group` varchar(255) NOT NULL,
`preview_text` text NOT NULL,
`badge_type` varchar(50) DEFAULT NULL,
`badge_text` text,
`badge_preview` text,
`quantity` int(10) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `discontinued` (`discontinued`),
FULLTEXT KEY `search_code` (`code`),
FULLTEXT KEY `search_name` (`name`),
FULLTEXT KEY `search_content` (`content`),
FULLTEXT KEY `search_brand` (`brand`),
FULLTEXT KEY `search_site_group` (`site_group`),
FULLTEXT KEY `search_article` (`article`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
|
Выполняется запрос (в не зависимости от данных внутри) примерно 3,5 сек.
Пробовал и дополнительные комбинированные ключи, и типы полей. Не помогает.
Как его можно оптимизировать?
Заранее спасибо! |