Доброго времени суток. Такая проблема, долго выполняется поиск (> 2 сек.), на сайте около 30 тыс. материалов. Есть ли возможность сократить? Ниже привожу запрос. С типом хранения данных InnoDB — запрос выполнялся 48 сек., сейчас изменил таблицу content на MyISAM — стало 2,5 сек.
Время запроса: 2311.88 ms После последнего запроса: 0.33 ms Память запроса: 0.077 MB. Память перед запросом: 4.148 MB Возвращено строк: 20
SELECT
CASE WHEN LOWER(a.title) LIKE LOWER('%слово%') THEN 5 ELSE 0 END +
CASE WHEN LOWER(a.title) LIKE LOWER('%поиска%') THEN 5 ELSE 0 END AS relevance,a.title AS title, a.metadesc, a.metakey, a.created AS created, a.language, a.catid,CONCAT(a.introtext,a.fulltext) AS text,c.title AS section,
CASE WHEN CHAR_LENGTH(a.alias) != 0 THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug,
CASE WHEN CHAR_LENGTH(c.alias) != 0 THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as catslug, '2' AS browsernav
FROM db_content AS a
INNER JOIN db_categories AS c
ON c.id=a.catid
WHERE ((LOWER(a.title) LIKE LOWER('%слово%') OR LOWER(a.introtext) LIKE LOWER('%слово%') OR LOWER(a.fulltext) LIKE LOWER('%слово%') OR LOWER(a.metakey) LIKE LOWER('%слово%') OR LOWER(a.metadesc) LIKE LOWER('%слово%'))
AND (LOWER(a.title) LIKE LOWER('%поиска%') OR LOWER(a.introtext) LIKE LOWER('%поиска%') OR LOWER(a.fulltext) LIKE LOWER('%поиска%') OR LOWER(a.metakey) LIKE LOWER('%поиска%') OR LOWER(a.metadesc) LIKE LOWER('%поиска%')))
AND a.state=1
AND c.published = 1
AND a.access IN (1,1,2,3,6)
AND c.access IN (1,1,2,3,6)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2021-06-08 16:24:40')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2021-06-08 16:24:40')
GROUP BY a.id, a.title, a.metadesc, a.metakey, a.created, a.language, a.catid, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id
ORDER BY relevance DESC, a.created DESC
LIMIT 20
План запросов (Explain)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ref idx_access,idx_state,idx_catid,catid,catid_2 idx_state 1 const 32529 Using where; Using temporary; Используется сортировка файлов
1 SIMPLE c eq_ref PRIMARY,idx_access PRIMARY 4 a.catid 1 Using where
Профиль
Status Duration
Starting 0.17 ms
Checking permissions 0.01 ms
Opening tables 0.04 ms
After opening tables 0.01 ms
System lock 0.01 ms
Table lock 0.02 ms
Init 0.08 ms
Optimizing 0.05 ms
Statistics 0.19 ms
Preparing 0.06 ms
Creating tmp table 0.06 ms
Sorting result 0.02 ms
Executing 0.01 ms
<b>Sending data 2307.93 ms</b>
Creating sort index 2.14 ms
Removing tmp table 0.54 ms
Creating sort index 0.04 ms
End of update loop 0.02 ms
Query end 0.01 ms
Commit 0.01 ms
Closing tables 0.01 ms
Unlocking tables 0.01 ms
Closing tables 0.04 ms
Starting cleanup 0.01 ms
Freeing items 0.04 ms
Updating status 0.06 ms
Reset for next command 0.02 ms
