How to make faster query to database for search

[Maciej]

Hello, I have problem with search results because database is waiting over 2 sec for non cached query

[Tech Support]

Hello, for faster search we recommend using SphinxSearch: https://www.kernel-scripts.com/en/articles/kvs-and-sphinx-integration/

But not sure if it is working with Arab language, please check its features.

Also you may need to switch off similar searches block, as the queries that you've posted are from it. This block slows down your search results, and there is no fix for this.

[Maciej]

On which file I will find this query 

select count(*) from ktvs_stats_search where ?=? and query_length<=? and MATCH(query) AGAINST (? ) and query!=? and query_results_total>=?

 

[Tech Support]

You can find it in /blocks/search_results/search_results.php

[Maciej]

For other customers - I solved problem with 3 mln records in ktvs_stats_search,

I changed this table from Innodb to Myisam, and I disable updating counts for search:

//foreach ($result as $date=>$result_date)
//{
//    foreach ($result_date as $query=>$result_query)
//    {
//        $sql_query_results='';
//        if (intval($result_query['query_results_videos'])>0)
//        {
//            $sql_query_results.='query_results_videos='.intval($result_query['query_results_videos']).', ';
//        }
//        if (intval($result_query['query_results_albums'])>0)
//        {
//            $sql_query_results.='query_results_albums='.intval($result_query['query_results_albums']).', ';
//        }
//        $sql_query_results.='query_results_total=query_results_videos+query_results_albums';
    //    if (!$result_query['amount'])
    //    {
            $result_query['amount']=0;
    //    }
//        if (sql_update("update $config[tables_prefix_multi]stats_search set amount=amount+?, added_date=?, $sql_query_results where query_md5=md5(?)",$result_query['amount'],$date,$query)==0)
//        {
//            sql_pr("insert into $config[tables_prefix_multi]stats_search set amount=?, query=?, query_md5=md5(query), query_length=length(query), added_date=?, $sql_query_results",$result_query['amount'],$query,$date);
//        }
//    }

in cron.

 

Another thing I disabled boolean mode from similar searches, now i have results >0.2 sec (before was 2 sec)

[Tech Support]

Thanks for posting this. KVS uses MyISAM by default, so it looks like you changed all your tables to InnoDB before that.

With regard to disabling search stats, first of all you can do that from admin panel (Settings -> Stats settings). But without updating search stats you should consider that they will all be removed after some time. KVS doesn't keep search stats forever, they (if not manually created from admin) are kept for a certain period of time that you configure in stats settings. If you disable stats update, they will all be gone after some time.

If you want to keep them forever in the current state as you have, you should better make sure they are all switched to manually created:

update ktvs_stats_search set is_manual=1

PS: this is for 5.4.0 the latest version, previous versions may not have this flag and will automatically delete all search queries.

[mongoose657]

Is it confirmed that MyISAM is better than InnoDB for the ktvs_stats_search table? Is there a significant difference in performance?

[Tech Support]

I think MyISAM is definitely faster than InnoDB with regard to full text search. The main advantage in KVS for using InnoDB is that InnoDB doesn't use full table locking when doing updates. Which is probably not the case with stats_search table.

[mongoose657]

what other tables use full-text search?

[Tech Support]

It is not about tables actually, it depends on your KVS settings with regard to search logic. It can be possible to switch off, or switch on full text search in different parts of KVS.