I have a table with well over 10k rows. I am need to show the results and was going to use paginate() to help with the load. However, when calling the query all the results are being called and not just limiting by the paginate().
$category = Inventory::join('inventory_categories', 'inventory.sku', '=', 'inventory_categories.sku')
->select('inventory_images.image', 'inventory.id' , 'inventory.sku', 'inventory.name', 'inventory.price', 'inventory_categories.category')
->leftJoin('inventory_images', 'inventory.sku', '=', 'inventory_images.sku')
->where('inventory_categories.category', 'LIKE', '%'.$cat.'%')
->where('inventory.active', '=', '0')
->where('inventory.stock_quantity', '>', 2)
->paginate(16);
When doing an Event::listen on the query I get this in return: note the lack of Limit.
array (size=4)
0 => string 'select count(*) as aggregate from `inventory` inner join `inventory_categories` on `inventory`.`sku` = `inventory_categories`.`sku` left join `inventory_images` on `inventory`.`sku` = `inventory_images`.`sku` where `inventory_categories`.`category` LIKE ? and `inventory`.`active` = ? and `inventory`.`stock_quantity` > ?' (length=321)
1 =>
array (size=3)
0 => string '%SOMETHING%' (length=11)
1 => string '0' (length=1)
2 => int 2
2 => float 82325.11
3 => string 'mysql' (length=5)
The table has 30,606 products that meet this query. So it's basically loading every row before showing the page which takes forever (I think the float in the array is the time it took). I need to limit the results and show the paginate properly. I tried using take() and that didn't work. Is this something new with Laravel 4.2.2? Because I am pretty sure this use to work in the past with no problems. But I would have to check my older projects to be sure.
I was able to get this to work by adding a composite index to the Mysql database for each table.