Laravel RAW query is orders of magnitude slower than the same query outside of Laravel

Question

I'm using Laravel 5.2 and PHP 7.0.6 on Debian. MySQL is 5.5.30 on a remote server.

When I run the query through HeidiSQL or from the command line client it executes in about 0.2 seconds. Executing the exact same SQL in Laravel (debug mode on or off) takes nearly 500 seconds. I tried pulling the Laravel query out of the MySQL query log and running it, and it ran fast. It's only when executing the statement through Laravel that it's slow. The laravel code is below. My timer code is before and after this line, so the slowdown is definitely here. The query only returns one row.

$results = \DB::select($sql);

An anonymized version of the query is below (business-specific data would otherwise be visible). Hopefully I haven't mangled anything in the process.

SELECT  ll.id,
    ll.other_id,
    ll.third_id,
    ll.created_at,
    ll.h_id,
    ll.sub,
    ll.sub2,
    ll.status,
    px.created_at,
    b.abbr,
    ldl.transaction_id,
    ldl.purchase_price,
    CONCAT(ld.first_name, ' ', ld.last_name) as fullname,
    lcase(ll.email_address) as email_address,
    ll.total_revenue

FROM    table1 ll
INNER JOIN table2 ld on ll.id = ld.fid
INNER JOIN table3 ldl on ll.id = ldl.fid
LEFT OUTER JOIN table4 px on ll.id = px.fid
INNER JOIN table5 b on ldl.bid = b.id

WHERE ll.created_at > '2016-05-04 00:00:00'
AND ll.created_at < '2016-05-04 23:59:59'
AND v_id IN (41,42,43,45,46)
AND ldl.b_id IN (131)
AND lcase(ll.email_address) in ('[email protected]')
AND ll.status = 'ACCEPTED'

Answer 1

I experienced this issue and finally found a solution. I had a query that when I ran on the command line it executed in about 40ms. When I put the same exact query into Laravel using:

DB::select( $myQuery ); // Where $myQuery is the exact string I ran on the command line

...the query took about 1 minute. Something was clearly wrong.

In the end, it had everything to do with parameter binding. By simply changing this:

DB::select("SELECT * FROM users WHERE username='Andrew'");

...to this:

DB::select("SELECT * FROM users WHERE username = :name",['name' => 'Andrew']);

My problem was solved.