How to speed up search in Joomla?

Question

MySQL

Good day. Such a problem, the search is performed for a long time (> 2 seconds), there are about 30 thousand materials on the site. Is there a possibility to reduce? Below is the request. With the data storage type InnoDB - the query was executed for 48 seconds, now I changed the content table to MyISAM - it became 2.5 seconds.

Время запроса: 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

Answer 1

Folded neater

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

It’s hard to figure out such a mess, especially without tables, and to advise something - in general xs ...
At first glance - a dozen wildcard likes - an idea for a C grade with a minus ... it also seems that there is a bunch of logic that could be easily executed from the puff side, thrown into the request, which also does not add speed ...
Well, a group buy over a bunch of fields is also a so-so idea ...
It looks like something went wrong either at the storage design stage, or that’s how it is on its own...
In any case, you can cut off pieces of the request, finding out where your main plug is, and after that you can see what and where to twist.