Joomla Smart Search Causing High Database Load – How to Optimize?

[sandormatyas]

Hello,

I run a Joomla 5.2.5 site with approximately 3,500 articles.
Smart Search is enabled, and I use YOOtheme Pro as the template.

Lately, the site has been getting slower than usual, so I checked the logs and discovered that it generates over 1GB of slow query logs daily!
Smart Search runs queries like this:

Code: Select all

SELECT l.link_id, l.object,l.start_date AS ordering
FROM xj56h3_finder_links AS l
INNER JOIN `xj56h3_finder_links_terms` AS m ON m.link_id = l.link_id
WHERE `l`.`access` IN (1,5) AND l.state = 1 AND l.published = 1 AND (l.publish_start_date IS NULL OR l.publish_start_date <= '2025-03-15 20:54:00') AND (l.publish_end_date IS NULL OR l.publish_end_date >= '2025-03-15 20:54:00') AND `object` != '' AND m.term_id IN ($list1)
GROUP BY l.link_id,l.object
HAVING SUM(CASE WHEN m.term_id IN ($list2) THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN m.term_id IN (2017763,2228629,3767504) THEN 1 ELSE 0 END) > 0;

where $list1 can contain more than 10,000 records.

In the database:

#__finder_terms contains 230,000 records,
#__finder_links contains 3,400 records,
#__finder_links_terms contains 1.5 million records.
We are using PHP 8.3.3 and MariaDB 10.4.22.

How can we reduce this heavy load?

[stutteringp0et]

Run the indexer as a cron job using the Joomla CLI.

I run my indexer in a cron job daily at midnight using this command:

/path/to/php /path/to/joomla/cli/joomla.php finder:index

[sandormatyas]

Hi,

The problem is not with the indexing but with the search itself.
I enabled debugging and opened a search page:
https://mydomain.com/search?q=test&opti ... iew=search

I found these huge queries on the Queries tab of the debug bar, and it shows that the memory usage is over 500 kB and the execution time is around 2 seconds per query.

[Pavel-ww]

Hi.

I think you should use a different search engine.

Smart Search has issues when used on large websites, those that you are talking about.
https://docs.joomla.org/Smart_Search_on_large_sites

Look at the https://extensions.joomla.org/extension ... ticsearch/

[sakiss]

Try disabling "Search For Phrases". This alone was making the table #__finder_terms 23 times larger.
Do note that you search will work exactly the same way (except that the suggestions will be single words).

[sandormatyas]

Try disabling "Search For Phrases". This alone was making the table #__finder_terms 23 times larger.
Do note that you search will work exactly the same way (except that the suggestions will be single words).

It's already disabled

[sandormatyas]

Smart Search has issues when used on large websites, those that you are talking about.
https://docs.joomla.org/Smart_Search_on_large_sites

Hi Pavel.
I know this article but it's about indexing performance. I don't have any issue about indexing, but the search itself.

I think you should use a different search engine.

This is what I try to avoid if it's possible somehow

[Pavel-ww]

I know this article but it's about indexing performance. I don't have any issue about indexing, but the search itself.

Hi.

Perhaps you didn't read the article to the end. Please pay attention to the section on Out of Disk Space Issues.

There is no solution for Smart Search. The volume of articles on your site is too large for it.

Unfortunately overall, Smart Search is useless. It is not needed for small static websites, and it is not designed for constantly growing and large ones.

Websites with up to 1000 articles are where it can be used.

[ceford]

Websites with up to 1000 articles are where it can be used.

I have a site with just over 5000 articles where I use Smart Search quite often. No problems so far!

[Pavel-ww]

Websites with up to 1000 articles are where it can be used.

I have a site with just over 5000 articles where I use Smart Search quite often. No problems so far!

Hi @ceford. Thank you. In that case, share your experience. Personally, I have stopped using Smart Search due to all the problems described above.

[ceford]

It is my Documentation site: https://jdocmanual.org/

The Smart Search Index page says there are 5418 items. I did not do anything special other than create a custom plugin and disable all the standard plugins except categories. I often use the this and that type of search.