@zenichanin , no I didn't figure it out, I did find a workaround. I created a store procedure with mysql query and called that procedure instead. It worked perfectly... Why? I don't know! Good luck, let me know if you figure it out...

How fast it is with phpmyadmin? How long it is on the server? How many rows does this return?

But just looking at the query, the database needs to be better structured for your purpose. Queries like this are just unmanagable.

@pmall, regardless of the query or database structure, why would it run so much slower in Laravel?

Here's my example:

Actual query:

select * from `websites` left join `keywords` on `websites`.`id` = `keywords`.`website_id` left join `positions` on `keywords`.`id` = `positions`.`keyword_id` where `websites`.`user_id` = '5'

Laravel code (v5.0):

DB::table('websites')
                    ->select('*')
                    ->leftJoin('keywords', 'websites.id', '=', 'keywords.website_id')
                    ->leftJoin('positions', 'keywords.id', '=', 'positions.keyword_id')
                    ->where('websites.user_id', $user->id)
                    ->get();

Debug bar shows this query took: 99.81ms

But in phpMyAdmin it shows same query taking 0.0008 seconds which equals to 0.8ms. That's a HUGE difference.

Both are running local, on same DB, same server, same everything.

Don't know it is strange. How many records do you have in this table?

Total records for each table in my local:

users table = 8
websites table = 9
keywords table = 238
positions table = 5793

When I run the query, it returns 5793 records in both Laravel and phpMyAdmin. The only difference is the performance. Unless Debugbar's calculations are wrong in regards to how long a query took to run.

Debugbar is probably slowing things down a bit, though it alone shouldn't account for that big of a time difference.

I wonder if phpmyadmin is employing some level of query caching, and that's not happening within laravel.

I agree though, the DB needs to be restructured, or at a minimum, that query completely rewritten. It would probably be more performant to run multiple queries rather than one big query with nested selects (which cause the total record set number to increase exponentially).

If you can find a way to rewrite it with joins instead of the nested select, you should get a massive performance boost.

@willvincent, my query is not using any nested selects and still experiences the same performance issues...

See https://laracasts.com/discuss/channels/laravel/controller-response-very-slow-on-query-that-runs-fast-in-phpmyadmin/replies/165602

I would check phpMyAdmin measurements against another tool like DbVisualizer. The numbers scale difference do not seem to make sense. Or I am just not reading numbers well...

Another plot twist.

Using the DB::statement I was able to cut down the time by 2/3rds.

Examples:

$websites = DB::table('websites')
                    ->select('*')
                    ->leftJoin('keywords', 'websites.id', '=', 'keywords.website_id')
                    ->leftJoin('positions', 'keywords.id', '=', 'positions.keyword_id')
                    ->where('websites.user_id', $user->id)
                    ->get();

This took 104ms

$websites2 = DB::select(DB::raw("select * from `websites` left join `keywords` on `websites`.`id` = `keywords`.`website_id` left join `positions` on `keywords`.`id` = `positions`.`keyword_id` where `websites`.`user_id` = '5'"));

This took similar time, 107ms

$website3 = DB::statement("select * from `websites` left join `keywords` on `websites`.`id` = `keywords`.`website_id` left join `positions` on `keywords`.`id` = `positions`.`keyword_id` where `websites`.`user_id` = '5'");

But this one took 29ms. Although it's much much faster, it is still slow compared to only 0.8ms it takes in phpMyAdmin.

I realize this is old but I believe I know what the problem is here. It's related to parameter binding. In my case, adding parameter binding changed my query from like a 1 minute run time to 40ms. More details here: https://stackoverflow.com/questions/37573942/laravel-raw-query-is-orders-of-magnitude-slower-than-the-same-query-outside-of-l/56379285#56379285