Simple Eloquent query taking too long time to execute

user_7095371 0
Viewed 1491

I have 2 queries. Even though the first one is more complicated and pulls much more data it takes only 154 ms to execute, meanwhile the second one takes 1.76 s to execute.

First (executing fast):

$offers = Offer::select(\DB::raw('tbl_offer.offer_id as sys_id, 
                                  tbl_offer.offer_name, 
                                  tbl_offer.preview_url, 
                                  COALESCE(tbl_offer.is_allow_website_links, 
                                  false) as is_allow_website_links, 
                                  tbl_offer.is_require_approval, 
                                 tbl_relationship.fk_relationship_status_id, 
                                  tbl_offer.is_private,
                                  tbl_offer.currency'))
                        ->leftJoin('tbl_relationship', function ($q) use ($affiliateId) {

                        $q->on('tbl_offer.offer_id', '=', 'tbl_relationship.fk_offer_id')
                          ->where('tbl_relationship.fk_affiliate_id', '=', $affiliateId);})
                          ->whereIn('fk_offer_status_id', [ 18, 19 ])
                          ->where('is_display', 1)
                          ->where('tbl_offer.is_trd_deleted', 0)
                          ->orderBy('offer_name')
                          ->get();

Second (executing slowly):

$currencies = Currency::select(\DB::raw('DISTINCT currency_code_from AS currency'))
                 ->where('sys_name', 'openexchangerates')
                 ->orderBy('currency')
                 ->get();
  1. What could possibly be the issue?
  2. Do you have any ideas on how to decrease loading time?
eloquent laravel mysql sql
3 Answers

first of all you are using 2 queries into one.

This is the first query:

$currencies = Currency::where('sys_name', 'openexchangerates')
            ->orderBy('currency')
            ->get();

And this is another:

\DB::raw('DISTINCT currency_code_from AS currency')

In order to use both queries into one, you should use this:

$currencies = Currency::selectRaw('DISTINCT currency_code_from AS currency')
            ->where('sys_name', 'openexchangerates')
            ->orderBy('currency')
            ->get();

I hope this way will decrease the executing time.

user_75651390

Just leaving this answer as it might be useful for the people who already tried applying indexing and query optimization, but didn't manage to reduce time significantly.

I have managed to reduce query loading time from 1.76 s to 0.127 s.

I have solved the problem by using some "walkaround". Since the currency rate is changing everyday for every single available currency, I am just getting the biggest currency_rate_batch_id, and getting all the currencies associated with this id (allows me to quickly get all distinct currencies).

However, I will apply indexing (as @Josh suggested) and avoid double queries throughout the project (as suggested by @Nicolas).

user_70953710

As @Nikolas said changing from select(DB::raw.. to selectRaw(... will help with the speed.

The other thing that you will want to check is your indexing on the main columns of your table.

I am assuming that you are using Mysql and so look at the below docs on indexing

https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html

Having indexes on the key columns of tables can make a big difference to the speed of queries

The Docs have details about adding indexes through migrations here:

https://laravel.com/docs/5.5/migrations#indexes

user_106432920
Related