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.