Search Analytics admin page is dead page (Amasty_Xsearch module)

Search Analytics admin page is dead page (Amasty_Xsearch module)

Hello,


Just imagine if amasty_xsearch_users_search db table has about 20mln records and Magento's search_query - only about 500k.

There is a method getSearchQueries in Amasty\Xsearch\Model\ResourceModel\UserSearch\Collection class that builds sql query to get data for Search Analytics page. And due to user_key has type varchar the main bottleneck is in that Mysql query:

  1. SELECT query.query_text, COUNT(query.query_text) AS total_searches, 
  2. COUNT(DISTINCT user_key) AS user_key, 
  3. query.query_id FROM amasty_xsearch_users_search AS main_table 
  4. LEFT JOIN search_query AS query ON query.query_id = main_table.query_id 
  5. WHERE (query_text IS NOT NULL) GROUP BY query.query_text ORDER BY total_searches DESC  LIMIT 10
It runs about 6 min on my local enviroment (i7-8750H) and needs about 20GB for mysql tmp table. Taking in account amount of data if we skip "COUNT(DISTINCT user_key)" sql part we would get 30sec that is mach better.
It looks like very old mysql bug.

Also I've tried adding indexes to amasty_xsearch_users_search db table or modify sql query as suggested by mysql in such cases but unfortunatelly have not got expected improvements.


For now I see next varianlts to resolve the issue:

  1. Change code logic of the methods getSearchQueries() and getMostWantedQueries and implement additional query for getting user_key quantity like:

    select count(1) from (select user_key from amasty_xsearch_users_search where query_id = 'QUERY_ID' group by user_key) as tmp;
  2. Remove the part of the page where these methods are used.
  3. Maybe upgrade Mysql on 6 version where COUNT(DISTINCT ..) queries works much better.

So, for now I'm looking for the best solution. Also I'll be happy to get additional advices.
Thanks!