Level 75
Please post your code correctly, please see
https://laracasts.com/discuss/channels/general-discussion/guidelines-for-posting-on-laracastscom
Jul 30, 2017
5
Level 1
Query Builder is rly slow?
So i make this query
$mainData = DB::table('table')
->where('id', $id)
->where('param1', '>', 0)
->where('param2', '>', 0)
->orderBy('param1', 'DESC')
->where('param1', '>=', DB::raw('UNIX_TIMESTAMP((LAST_DAY(DATE_SUB(NOW(), INTERVAL ' . intval($month) . ' MONTH))+INTERVAL 1 DAY)-INTERVAL 1 MONTH)'))
->where('param1', '<', DB::raw('UNIX_TIMESTAMP(LAST_DAY(DATE_SUB(NOW(), INTERVAL ' . intval($month) . ' MONTH))+INTERVAL 1 DAY)'))
->get();
and when i call before this function time() and after dd(time()-$start); i see 3second cuz when i run this query in phpmyadmin(i get query by toSql()) and Query took 0.0015 seconds. cuz why eloquent is so slow?
Level 1
@jlrdw look now :)
Level 75
- First does the query give correct results
- Second, try using getPdo() and see if faster (just for test)
Here is example of usage
$sql = "SELECT * from pets where petname like :petname";
$sth = DB::connection()->getPdo()->prepare($sql);
$sth->execute(array(':petname' => 'c%'));
$results = $sth->fetchAll(\PDO::FETCH_ASSOC);
print_r($results);
///or/////
foreach ($results as $row) {
echo $row['petname'] . '<br>';
}
You will need this use statement top of controller
use Illuminate\Support\Facades\DB;
Level 1
is not faster, time is same ;/
Level 122
Not sure why, but param1 has to be greater than or equal one date time, less than another dateTime and also greater than 0 - it seems to be overkill?
How many rows do you have?
Have you indexed param1?
calculate the target date outside of the query then use where between?
...but hang on, where('id',$id) ? - if this is your primary key then the WHOLE of the rest of the query is irrelevant?
Post your real code and we can consider further.
Please or to participate in this conversation.