Join function in the query making the query run slower

user_5346880 0
Viewed 58

I have two table both contains data around 200,000. I have write a query like below to retrieve data using some joins.

This is the query I have tried

$dbYTD = DB::table('stdtsum as a')
            ->join(DB::raw("(select distinct s_id, c_cod, compid from stdcus) b"), function($join){
                $join->on('a.compid', '=', 'b.compid')->on('a.c_cod', '=', 'b.c_cod');
            })
        ->select('b.s_id', DB::raw('sum(turnover) as sumturn'))
        ->whereBetween('date', [$startYTD, $endYTD])
        ->groupBy('b.s_id')
        ->get()
        ->toArray();

This query is giving result correct but the process time it takes is very long, sometimes it even timesout.

Can anybody help me how can I optimize this query?

laravel-5 mysql sql php
1 Answers

You'll need to index all your columns on which join condition is applied. In your case : "compid" , "c_cod" in both your tables.

Generally, "Primary Key constraint" columns are automatically indexed in databases, though you'll have to index the "Foreign Key constraint" columns manually.

Some Indexing Tips :

  1. Create an index on the field that has the biggest variety of values first. This will result in the “most bang for your buck”, so to speak.
  2. Keep indexes small. It's better to have an index on just zip code or postal code, rather than postal code & country. The smaller the index, the better the response time.
  3. For high frequency functions (thousands of times per day) it can be wise to have a very large index, so the system does not even need the table for the read function.
  4. For small tables an index may be disadvantageous. The same can also be said for any function where the system would be better off by scanning the whole table.

Remember that an index slows down additions, modifications and deletes because the indexes need to be updated whenever the table is. So, the best practice is to add an index for values that are often used for a search, but that do not change much. As such, an index on a bank account number is better than one on the balance.

Tips reference : https://www.databasejournal.com/features/mysql/article.php/3840606/Maximizing-Query-Performance-through-Column-Indexing-in-MySQL.htm

user_74728010
Related