• I read the Generative AI policy and my contribution is either not created with the help of AI or is compatible with the policy and GNU/GPL 2 or later.

Summary of Changes

Smart Search creates an index of all items it is supposed to search through either by indexing it directly when saving the item or by mass-indexing it in the Smart Search component itself. During that indexing process, it splits up the words into tokens, does some processing and saves them in the database in the #__finder_tokens table. From there it copies them over to the #__finder_tokens_aggregate table and sums them up to create the weights for the actual searching. From there the tokens are inserted or updated in the different permanent tables Smart Search uses. As you might have guessed by the word "permanent", the two previous tables are only temporary tables.

These temporary tables are using the MEMORY type in MySQL, which are very fast, but can run into issues. If the document is very large, the number of tokens written to the tables can overrun the tables, resulting in a fatal error. For that case, we currently have a method in place to switch to a "normal" InnoDB table. That makes indexing a lot slower, but at least it works, right? Not exactly. If you are running on a MySQL cluster, you don't have the MEMORY tables available at all (#42414) and our code does not properly handle that.

At the same time, our indexing code is not safe against mutual interference. If two authors click on "save" at the same time, their content might not be indexed correctly and two separate indexing runs would basically completely mess each other up, since they are using the same tables and expect to have each table exclusively for themselfs.

The whole situation is very frustrating and also complicated. I first looked into using different tables and it turns out that MEMORY tables are probably the worst solution for this. A better solution would be temporary tables, something that both MySQL and Postgres both support. (Postgres does NOT have MEMORY tables) Temporary tables basically do the same thing as MEMORY tables, but they are not limited like MEMORY tables are. Instead, they will automatically switch over to hard drive when they become to big (and also back to memory) and they are only present per database session. So exactly what we need, right?

Yes, but why are we using a temporary database table at all? We already have all the data in memory and sending it to the database to sum up a few numbers before saving it to the permanent tables feels strange as well. Can we do all of this in PHP itself? Do we benefit from this? Short answer: yes. Long answer: it depends.

Benchmarks

I tested this new implementation on a customers site with ~4200 indexable items, cleared the index and ran the indexing on the CLI with the old and the new code. Phrase searching was disabled and stemming was enabled. The old code took about 5 minutes to completely index everything, the new code took about 4 minutes. This is a pretty good result, since about 75% of the runtime is eaten up by creating the result object, tokenizing the text, stemming everything and only 25% were storing the tokens in the database.

That sounded pretty good, so I chose a few additional test scenarios. First of all, from those 4200 indexable items, 300 were selected at random to compare the runtime and memory usage. Then I also used "War and Peace" from Tolstoi and the complete works of Shakespear and ran them both with Phrase searching enabled and disabled. These are the results:

In all cases, with the old and the new code, the index was the same after the respective run. The observations for me from these tests is:

1. Doing the whole processing of the indexing in PHP is almost always faster than when using the solution with MySQL. The only time where it is slower is in irrelevant test scenarios.
2. The additional memory consumption in PHP is neglectable, since it represents only a few percent of the overall consumption. Tokenizing the Shakespear text for example already requires 96MB of memory and the additional 1.9MB thus don't really matter
3. This gets rid of all the code to handle those temporary tables and thus also a lot of error points we have right now. (See #42414, #44492, #46470, probably #47531 and #47447)

Sidenote

I've tested this on a Windows machine. On a real webserver the gains would be slightly smaller, since the I/O on Linux tends to be better.

Implementation details

This PR switches the implementation from using the database as temporary storage and instead calculates the necessary data in PHP in memory. In order to prevent the memory running full for very large documents, there is a mechanism to flush the calculated data to the permanent tables at a certain threshold.

Testing Instructions

Long story short: You want a reasonably large set of data to index. If you have a few hundred articles, that would be good. Ideally, you would also have access to the CLI of your site, to run the tests there. The CLI will return the time and memory usage automatically. Alternatively you can run the normal indexing process in the backend component.

Possible test #1:

1. On the command line, type php cli/joomla.php finder:index purge THIS WILL CLEAR YOUR INDEX BEFORE RE-INDEXING!!
2. Note the duration of the indexing and the peak memory usage. Also note the number of rows in #__finder_links_terms and #__finder_terms.
3. Apply the changes from this PR and run the above command again.
4. Compare the numbers from step 2 with the numbers that you have now.

Possible test #2:

1. Go to Smart Search -> Index and clear the index.
2. Start the indexing process.
3. Note the number of rows in #__finder_links_terms and #__finder_terms.
4. Apply the changes from this PR.
5. Clear the index again.
6. Start the indexing process again.
7. Compare the numbers from step 3 with the numbers that you have now.

In both cases you should end up at the same numbers for the database tables. Ideally your indexing should take less time and less memory when these changes are applied.

Link to documentations

Please select:

• Documentation link for guide.joomla.org:

• No documentation changes for guide.joomla.org needed

• Pull Request link for manual.joomla.org:

• No documentation changes for manual.joomla.org needed