Introduction

While testing the performance of the smart search using a large amount of articles (10.000+) we noticed a big increase in response time. Checking the queries with the debug tool revealed two queries which were performing quite poorly, taking up around 1.5s of the total 1.7s response time (12.000 articles). This effect scales up with an increasing amount of articles. In this PR we replaced these two queries with more efficent ones, reducing the total response time of these queries to approximately 350ms (from 1.5s).

Summary of Changes

We replaced the old query, which was responsible for searching the db given one or multiple search terms and returning the weighted results:

SELECT l.link_id, l.object,SUM(m.weight) AS ordering
FROM #__finder_links AS l
INNER JOIN `#__finder_links_terms` AS m ON m.link_id = l.link_id
WHERE `l`.`access` IN (:preparedArray1,:preparedArray2,:preparedArray3) AND l.state = 1 AND l.published = 1 AND (l.publish_start_date IS NULL OR l.publish_start_date <= '2021-08-24 12:03:00') AND (l.publish_end_date IS NULL OR l.publish_end_date >= '2021-08-24 12:03:00') AND m.term_id IN (18,103)
GROUP BY l.link_id,l.object
HAVING SUM(CASE WHEN m.term_id IN (18) THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN m.term_id IN (103) THEN 1 ELSE 0 END) > 0
ORDER BY ordering DESC LIMIT 20

With an updated, faster query:

SELECT l.link_id, l.object,m.weight AS ordering
FROM (
	SELECT link_id, SUM(weight) as weight
	FROM #__finder_links_terms
	WHERE term_id IN (18,103)
	GROUP BY link_id
	HAVING COUNT(link_id) > 1) AS m
INNER JOIN `#__finder_links` AS l ON l.link_id = m.link_id
WHERE `l`.`access` IN (:preparedArray1,:preparedArray2,:preparedArray3) AND l.state = 1 AND l.published = 1 AND (l.publish_start_date IS NULL OR l.publish_start_date <= '2021-08-24 12:04:00') AND (l.publish_end_date IS NULL OR l.publish_end_date >= '2021-08-24 12:04:00')
ORDER BY ordering DESC LIMIT 20

The difference in performance comes from grouping the data before joining the two large tables. We also reduced the GROUP BY to only group by l.link_id, since there are no duplicate l.object for any given l.link_id because both are part of the #_finder_links table, where link_id is the primary key.

This also has the positive side effect, that the new query is more efficient to use with the _getListCount($query) of the BaseDatabaseModel-class.

These changes were implemented in the getListQuery() of the SearchModel-class. We've also refactored the method to work with the new query.

Testing Instructions

Test this on a clean version of 4.0

1. Create a large amount of articles (overload), preferably 10k+.
2. Activate the debug tool: System -> Global Config -> Debug System on
3. Create a smart search Content->Site Modules->New->Smart Search , select a position
4. Test the search with various terms ('et' should be the worst case for overload), also try the advanced filters
5. While testing take a look at the debug tool and note the time
6. Checkout this PR and test the search again (step 4&5)
7. Check for difference in loading time and if the results are the same

Actual result BEFORE applying this Pull Request

The loading time is pretty slow (approximately 1.5 s for 12k articles). These were the two responsible queries before the changes:

Expected result AFTER applying this Pull Request

The loading time should be significantly lower and the search results should be the same. Also these two queries should appear:

Documentation Changes Required

Probably not.

Contributers

@socke300 @Waleet

Thanks to @Hackwar for answering our questions.