0

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!

Sredny M Casanova
  • 4,735
  • 21
  • 70
  • 115
  • Why not write the query using Laravel's query builder? Rough Eg: `$query = DB::table('TableA s') ->leftJoin('tableB', function($join) { $join->where('TableB.id', '=', '1'); $join->on('TableA.mid', '=', 'tableB.mid'); }) ->leftJoin(/***/); ` – Rob W Dec 01 '16 at 19:39
  • @HalfCrazed yes, I did it, but I need to use `FORCE INDEX FOR JOIN` to improve the speed of the query.I have thousand of registers and that really makes the difference – Sredny M Casanova Dec 01 '16 at 19:43
  • Use `dd(DB::select(DB::raw(TheQueryAbove))->toSql());` to dump it – Antonio Carlos Ribeiro Dec 01 '16 at 19:54
  • There is an extra *"* character in you code sample. – Markus Dec 01 '16 at 19:56
  • @AntonioCarlosRibeiro yes, but nothing it stays loading and loading – Sredny M Casanova Dec 01 '16 at 20:00
  • @Markus yes, my bad! but it's not the cause, I just pasted it wrong here – Sredny M Casanova Dec 01 '16 at 20:00
  • `LEFT JOIN` have been reported to be slower than `INNER JOIN` (See http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server). You should also use index. This is more a MySQL question than a Laravel question – Wistar Dec 01 '16 at 20:31
  • It also depends on the size of your tables, the spec of your server and your MySQL configuration. You could try to see if you optimized your MySQL configuration with this http://mysqltuner.com/ – Wistar Dec 01 '16 at 20:33
  • @SrednyMCasanova I see. Check this out: https://github.com/laravel/internals/issues/33#issuecomment-245178924 - might help you build queries instead of using static queries. – Rob W Dec 02 '16 at 15:24

0 Answers0