Huge oro_website_search_integer table causes CustomerUser creation slowdown

Question

We have an Orocommerce 4.2.4 application currently in production, and we have a very large amount of products (about 340,000 simple products attached to thousands of configurable products). We do not directly use the Orocommerce frontend for end-users, as we have our own frontend which interfaces with the Orocommerce REST API.

We have found that lately our application responded very slowly when an user created their account. Through the Symfony debugger we discovered that the user creation process (flushing a new CustomerUser) took over 30 seconds to complete because of a single SQL query that queried the oro_website_search_integer table.

We then tried looking for that table, and discovered that over 2,000,000 items were inside, which rather clearly explained why the query was so slow. It seems to contain data related to search indexing, and for every product created, a few entries are added.

We tried on a test environment to empty that table, and it solved the performance issue, with seemingly no adverse effects (be it on the user-facing frontend or the admin backend).

Here are my questions concerning this matter:

• Is it possible to stop the CustomerUser entity flush from triggering this SQL query? I think this is caused by a Doctrine PostPersist event, though I'm not 100% sure.
• Is it safe to empty the oro_website_search_integer table? Can it have adverse effects?

Answer 1

The community edition of OroCommerce is not intended to be used with a large amount of data, and it is recommended to switch to an enterprise edition.

Is it possible to stop the CustomerUser entity flush from triggering this SQL query? I think this is caused by a Doctrine PostPersist event, though I'm not 100% sure.

You can find all the triggered event listeners in the symfony toolbar profiler for the customer user change event. There should be the one that triggers the query. Most likely, the listener is defined in the OroSearchBundle or OroWebsiteSearchBundle. You can disable the original listener with the compiler pass or override it and add an exclusion, as the listener is, most likely, the same for all the entities.

Is it safe to empty the oro_website_search_integer table? Can it have adverse effects?

The search functionality uses this table. If you clear the table, some search features will not work as expected, but in general, it is safe to clear the table as you can regenerate it again with the search reindex commands