Catalog Search Database Issues - Cleaning Out Useless Data

[mwolff9328]

We recently encountered an issue on our Magento site a believe we encountered some type of security or SPAM attack on the search feature of our site, which was overpopulating our mage_catalogsearch database with useless data.

Below is the data for the following tables in phpMyAdmin:

phpMyAdmin (database teamss5_mage1)

mage_catalogsearch_query       1,782,506        InnoDB utf8_general_ci       215.5 MiB    
mage_catalogsearch_result       700,538           InnoDB utf8_general_ci         47.6 MiB      

You can see that these rows are storing a considerable amount of data. We are trying to keep our database size down as low as possible to allow for optimal site performance.

When logging into the Magento admin, we are also noticing that when navigating to "Catalog > Search Terms" there are 89,126 pages and a total of 1,782,506 records found. Which seems to be an incredibly unusual large amount of data.

Does anyone have any idea what could have caused this and how we can clean out these pages and records so we can keep our Magento site optimized. Is it possible to truncate or delete these records without affecting our website and causing it to crash? And if so how?

Please let us know what can be done to resolve this issue, any assistance is greatly appreciated!


You can also see the below image link screenshots to better understand the issue:

http://www.elevatemediastudio.com/support/ssc/catalog-search-terms-page.jpg

http://www.elevatemediastudio.com/support/ssc/admin-search-terms-menu.jpg

http://www.elevatemediastudio.com/support/ssc/php-admin-view.jpg

[Binod Yadav]

Hello @mwolff9328 

In magento 1.X, when we search any keyword on the site it should be stored in the catalogsearch_query table. So if you truncate the tables the you will be only loose the search terms from the site, which is showing in admin area. 

Yes, It safe to Truncate these tables, there are three tables related to search, so you can Truncate it.

TRUNCATE catalogsearch_query;

TRUNCATE catalogsearch_fulltext;

TRUNCATE catalogsearch_result;

NOTE : It's safe to truncate it but if you had previously created synonyms or redirects you will loose them. So option, you can use the Delete query at this moment.
e.g

 DELETE FROM  `catalogsearch_query` WHERE  `synonym_for` IS NULL AND  `redirect` IS NULL` 

And this will only delete rows that don't have a value in them for synonym or redirect.

[mwolff9328]

Yes, when we try to truncate the "catalogsearch_query" we get the
following error message pop up:

See image link below:
http://www.elevatemediastudio.com/support/ssc/truncate-error.jpg

If we use the provided statement below, this will not affect or break our
website in any way?

DELETE FROM `catalogsearch_query` WHERE `synonym_for` IS NULL AND
`redirect` IS NULL`

And should this query be run in the phpMyAdmin > SQL tab?

Also, can we truncate the "catalogsearch_result" the same way and if so
what statement would we use for that?

[Binod Yadav]

Hello @mwolff9328 

Please run this query on  SQL Tab from PHPMyAdmin. As per your error, it's Foreign key constraint with child tables.

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE catalogsearch_fulltext;
TRUNCATE catalogsearch_result;
TRUNCATE catalogsearch_query;
SET FOREIGN_KEY_CHECKS=1;

[mwolff9328]

I tried to run the SQL Query and received the following error:

SQL query:

TRUNCATE catalogsearch_fulltext

MySQL said:   

#1146 - Table 'teamss5_mage1.catalogsearch_fulltext' doesn't exist  

Below are the tables I see available in my database:

mage_catalogsearch_fulltext  - 87.3KiB

mage_catalogsearch_query - 215.5 MiB

mage_catalogsearch_result - 47.6 MiB

[Binod Yadav]

Hello @mwolff9328 

Please run it, you using the prefix "_mage".

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE mage_catalogsearch_fulltext;
TRUNCATE mage_catalogsearch_result;
TRUNCATE mage_catalogsearch_query;
SET FOREIGN_KEY_CHECKS=1;

[mwolff9328]

Is it possible to do this without truncating the data that contains synonyms and redirects?

We want to make sure any "legitimate" or "non-junk" search terms, which appear as hints when users interact with the search form remain.

Also that we don't delete any custom search term redirects that may have been configured to return specific pages for certain searches, rather than search results.

Since we believe most of the data is junk due to the spam attack we don't know what data is good or bad, so we only wan't to try and remove the junk data.

[Binod Yadav]

Hello @mwolff9328 ,

I have already suggested to you, use the delete sql query with specific condition, if you aware the sql statement.
OR ,You can delete records from admin selection.
Best option, you can contact any Magento professional developer.

[mwolff9328 · Accepted solution]

Thank you for your assistance! We used your delete solution using the MariaDB: DELETE Statement syntax and it solved our issue.

Updated file sizes are displayed below, and all of the records are no longer displaying in the Catalog > Search Terms section of the Magento admin.

mage_catalogsearch_fulltext - 87.3 KiB
mage_catalogsearch_query - 64 KiB
mage_catalogsearch_result - 48 KiB

The statement we used is below. Thank you again for all your help! 

DELETE FROM mage_catalogsearch_query
WHERE `synonym_for` IS NULL
AND `redirect` IS NULL;