Jump to content

How to make faster query to database for search


Maciej
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...