xTRIFAC70Rx xTRIFAC70Rx - 7 months ago 71
MySQL Question

Laravel 5 Queries really slow

I am new to laravel and im not sure if i am doing something incorrect as my queries take extremely long to run.

I have around 35000 records, please see below an example of a query that takes around 23 seconds to load the page (see screenshot link below).


Can anyone advise on my mistake or a better way to query my DB. I have tried Eloquent and its a few ms slower than a DB:: Query.

$data3 = DB::table('toutcome')

->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
->join('tapplicant', 'tapplicant.AppID', '=', 'toutcome.AppID')
->select(DB::raw('DATE_FORMAT(CompletedDate, "%d %M %Y") as CompletedDate,
SUM(AffID = "MW0050") as leadccount50,
SUM(AffID = "MW0051") as leadccount51, tapplicant.AppReference'))
->groupBy(DB::raw('DATE_FORMAT(CompletedDate, "%d %M %Y")'))


Firstly that sounds really long.

The queries do look quite detailed, but it shouldn't take longer than 1 minute.

You could try using eloquent, but this will be only a little quicker than the raw queries.

Things you didn't mention are :

Is this a local server or remote server ? If you are using a remote server, my solution would be to use the "skip-name-resolve" in your my.ini / my.cnf under mysqld and update your key_buffer_size.

If this does not improve the speed, maybe look at your resources for that particular server.