laravel很慢_优化Laravel Eloquent whereHas（）查询 - 非常慢

我正在使用Laravel 5.4

我有3个型号：Order，OrderLine和Product .

订单hasMany()OrderLines OrderLine hasOne()产品通过OrderLine模型中的product_id(我已经正确索引了这个，至少我认为！)

我的要求是检索产品用于特定品牌名称的所有订单和订单线 .

这是我雄辩的查询 . 我知道查询有效但是当放在一个大型数据集上时它似乎无限运行(大约10,000个Orders，12,000个OrderLines / Products)

$orders = Order::whereBetween('order_date', [$this->start_date,$this->end_date])

->whereHas('lines', function ($q1){

$q1->whereHas('product', function ($q2){

$q2->where('brand', 'Brands>SanDisk');

});

})->with('lines')->with('lines.product')->get()->toArray();

通过toSql()方法进行调试时，会生成以下SQL .

select

*

from `orders`

where

`order_date` between ? and ?

and

exists (select * from `order_lines` where `orders`.`id` =`order_lines`.`order_id`

and

exists (select * from `products` where `order_lines`.`product_id` = `products`.`id` and `brand` = ?))

我创建表的3次迁移如下(为简单起见，我除了键之外删除了所有内容)：

Schema::create('orders', function (Blueprint $table) {

$table->increments('id');

});

Schema::create('order_lines', function (Blueprint $table) {

$table->increments('id');

$table->integer('product_id');

$table->integer('order_id');

});

Schema::create('products', function (Blueprint $table) {

$table->increments('id');

});

然后我添加了以下索引：

Schema::table('order_lines', function (Blueprint $table) {

$table->integer('product_id')->unsigned()->change();

$table->foreign('product_id')->references('id')->on('products');

});

EXPLAIN语法的结果如下：

1 PRIMARY orders ALL 91886 Using where

2 DEPENDENT SUBQUERY order_lines ALL 93166 Using where

3 DEPENDENT SUBQUERY products eq_ref PRIMARY PRIMARY 4 mymemory_main.order_lines.product_id 1 Using where