I've a Laravel 5.2 application, I need to make an advanced query to the database so I'm trying to use the DB::raw expression, mainly, my query is like this:
SELECT * FROM TableA AS s LEFT JOIN TableB AS l FORCE INDEX FOR JOIN (idx_1) ON ((s.mid = l.mid) AND (l.id = 1)) LEFT JOIN TableC AS f FORCE INDEX FOR JOIN (idx_C_1) ON (s.mid = f.mid) LEFT JOIN TableD as bl FORCE INDEX FOR JOIN (idx_D_1) ON ((bl.ACid = s.ACid) AND ((bl.userid = 1) OR (bl.userid = -1)) AND (bl.type = 1)) LEFT JOIN TableE AS wl FORCE INDEX FOR JOIN (idx_E_1) ON ((wl.sid = s.sid) AND ((wl.userid = 1) AND (wl.type = 2))) LEFT JOIN TableF AS gwl FORCE INDEX FOR JOIN (idx_F_1) ON ((gwl.sid = s.sid) AND ((gwl.userid = -1) AND (gwl.type = 2))) WHERE (bl.sid IS NULL) So, it just get stucked in Laravel and doesnt show me a response, so I executed it in PHPmyAdmin and the response is very very fast. In Laravel I have this:
$query=DB::select(DB::raw(TheQueryAbove)) ->orderBy('s.reversestamp', 'asc') ->offset(0) ->limit(26) ->paginate(10); So, why it takes so long? or how can I test it to find the cause? I already tried: dd(DB::select(DB::raw(TheQueryAbove)))->toSql(); with no success, it doesnt even get printed. Thanks!
$query = DB::table('TableA s') ->leftJoin('tableB', function($join) { $join->where('TableB.id', '=', '1'); $join->on('TableA.mid', '=', 'tableB.mid'); }) ->leftJoin(/***/);FORCE INDEX FOR JOINto improve the speed of the query.I have thousand of registers and that really makes the differencedd(DB::select(DB::raw(TheQueryAbove))->toSql());to dump it