1

My site having a more than 1.5 million records and my question is the how may i fetch record fast.

i am spending so much time to find the way for it finally find out the one way which is to apply the indexing on the field. also try to apply on that it's better result than before.

but still it taking to much time to getting data from the database can any one tell me other technique or trick which i applied on that so it give me much better performance. and my site is built in laravel 4.2.

You can see also my query like below

     $result = DB::table('bussiness')
                ->select(array('bussiness.*', DB::raw('COUNT(complaintreport.id) as reported')))
                ->where('bussiness.is_approved', 1)
                ->where('bussiness.status', 1)
                ->where(function($sql) use ($data) {
                    $sql->where('bussiness.bussiness_name', 'like', '%' . Str::upper($data['searchstring']) . '%');
                    $sql->orWhere('bussiness.trading_name', 'like', '%' . Str::upper($data['searchstring']) . '%');
                })
                ->leftjoin('complaintreport', 'complaintreport.bussiness_id', '=', 'bussiness.id')
                ->groupby('bussiness.id')
                ->get();

Thanks in advance.

4
  • 1
    You need to show the code of the slow query in order for others to propose ways to improve it.
    – Nicholai
    May 6, 2015 at 2:32
  • @nicholai : Thanks for reply and can you please check it out my question again i have updated with my query structure. which i have used. May 6, 2015 at 2:57
  • That is not really a query. It would be better to get the actual SQL being generated by that construct... and then use EXPLAIN SELECT ... to review the server's query plan... but certainly WHERE column LIKE '%search_string%' is always going to be a slower query because indexes provide very little benefit. The % at the beginning prevents any index on the referenced column from being used, if there is one. May 6, 2015 at 3:11
  • @michael-sqlbot : ok than what is the batter option for using other than % in my query can you please suggest me. May 6, 2015 at 3:16

1 Answer 1

1

What you have is a full table scan with no way to speed it up.

Look into FULLTEXT(bussiness_name, trading_name) indexing as a much faster way to search for words. You would need MATCH (bussiness_name, trading_name) AGAINST("$data['searchstring']" IN BOOLEAN MODE).

If status is rarely 1 and is_approved is rarely 1, then this would help: INDEX(status, is_approved).

You should also protect yourself from SQL injection by escaping the searchstring.

2
  • @rich james : thanks for reply i already applied this thing in my code now and it giving better performance than before but it still not giving me satisfactory response. can you please give me some other trick which you know about it than please let me know. May 6, 2015 at 5:28
  • Let's see the SELECT, EXPLAIN, and CREATE TABLE.
    – Rick James
    May 6, 2015 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.