Query optimization in laravel

Question

Can this query be optimized because it takes very long time to display results even if there is only row in tables.

$orders = Order::select(DB::Raw('orders.*, payments.*')) 
        ->leftJoin('payments', function($join) {
            $join->on('orders.order_id', '=', 'payments.orderID') 
                     ->orwhere('payments.processed', '=', '');            
          })
        ->orderBy('order_id', 'asc')
        ->get();

So here I query table order and table payments. Condition must check if there is matching 'orders.order_id', '=', 'payments.orderID'. Then I show results on the page and select columns from both tables.

I have no idea why this work so slow and it takes like 10 seconds to load page and show just 1 result. Other parts of the site are fast even they must show a lot more results from other tables.

o.v · Accepted Answer · 2016-08-31 14:35:12Z

1

This is enough simple query, so not much space for optimization. Judging from description try using INNER JOIN instead of LEFT JOIN. If you have a lot of data, check that necessary indices on columns (order_id, processed) are in place.

answered Aug 31, 2016 at 14:35

o.v

89511 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

o.v Over a year ago

Well, that means something is wrong with your data or its structure. Because you have orders and payments, but they are not related with each other.

S.I. Over a year ago

What I make there is select from orders + select from payments. This is because I want to check if there is any record for order_id. If there a record this means order is paid and I show Paid on the page. If there is no record means isn't paid. That's why I joining payment table and nothing seems wrong here.

Collectives™ on Stack Overflow

Query optimization in laravel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related