How to manage data in the amasty_xsearch_users_search table efficiently?

How to manage data in the amasty_xsearch_users_search table efficiently?

In order to maintain optimal database performance and manage data effectively in the Advanced Search module, you may need to truncate the amasty_xsearch_users_search table. Here’s how you can manually delete records or set up an automatic clean-up function.

Manually Deleting Records

To manually clear the amasty_xsearch_users_search table, the following SQL commands can be used:

To delete all records:
DELETE FROM amasty_xsearch_users_search;

To delete records older than 1 year:
DELETE FROM amasty_xsearch_users_search WHERE created_at < CURRENT_TIMESTAMP - INTERVAL 1 YEAR;

"YEAR" can be replaced with "MONTH", "WEEK", or "DAY" to adjust the time interval as needed.

Automatic Clean-up Setup

To ensure that the process of clearing old search requests is automated, it should be confirmed that the Advanced Search module is updated and configured correctly:

Step 1. Module Version Verification: It should be checked that version 1.19.0 or higher of the Advanced Search module is being used, as the feature for records auto-cleaning was added in this release. Assistance with the updating process can be sought by referring to this article.

Step 2. Enabling Auto-Cleaning: The Old Search Requests Auto-Cleaning setting should be enabled by navigating to Stores > Configurations > Amasty Extensions > Advanced Search > General tab:



Thus, the records older than the period specified in the Auto-Cleaning Period in Days field will be automatically deleted.
It should be outlined that deleting records from the amasty_xsearch_users_search table will not disrupt the extension's functionality. However, the analytics data based on the remaining records will be affected, as deleted records will no longer be available for historical analysis.