提高SQL查询速度
问题描述:
我使用MySQL和我有一样的模式:提高SQL查询速度
|------------|-------------|------------|--------------|
| cities |category_city| categories| companies |
|------------|-------------|------------|--------------|
| id | city_id | id | id |
| name | category_id | name |subcategory_id|
| | | parent_id | city_id |
| | | |...other cols |
|____________|_____________|____________|______________|
关系: 市同类别有->belongsToMany()
public function categories()
{
return $this->belongsToMany(Category::class);
}
类别有子类别:
public function subcategories()
{
return $this->hasMany(Category::class, 'parent_id', 'id');
}
我从类别和城市筛选公司,因为我需要当前的城市公司和我有一个全局范围:
public function getCompanies($city_id)
{
return $this->companies()->whereHas('mainCity', function ($q) use ($city_id) {
$q->where('city_id', $city_id);
});
}
mainCity方法:
public function mainCity()
{
return $this->belongsTo(City::class, 'city_id');
}
这里是我的方法执行与AJAX请求查询:
public function getPlaces(City $city, Category $subcategory, $north, $south, $east, $west)
{
$companies = $subcategory->companies()
->withCount('comments')
->companiesByBounds($north, $south, $east, $west)
->paginate(8);
$markers = $subcategory->companies()
->companiesByBounds($north, $south, $east, $west)
->get(['lat', 'lng', 'slug', 'title']);
return response()->json(['companies' => $companies, 'markers' => $markers], 200, [], JSON_NUMERIC_CHECK);
}
和byByBounds示波器方法:
public function scopeCompaniesByBounds($query, $north, $south, $east, $west)
{
return $query->whereBetween('lat', [$south, $north])
->whereBetween('lng', [$west, $east]);
}
在公司我有~2m记录。主要问题是查询花费3.5秒。请帮助改善我的疑问。
下面是该查询:
select count(*) as aggregate from `companies` where `companies`.`category_id` = '40' and `companies`.`category_id` is not null and `lat` between '53.68540097020851' and '53.749703253622705' and `lng` between '91.34262820463869' and '91.51600619536134'
答
要提高,你需要在列lat
和lng
添加索引的速度。
CREATE INDEX idx_lat ON companies (lat);
当列添加到条件时,索引用于查询中。
您是否尝试过使用EXPLAIN/Visual Explain运行结果SQL来查看执行计划? –
不,我没有。我怎样才能做到这一点? – pwnz22
我并不熟悉laravel,所以不知道如果http://*.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql- query-as-a-string将会为你提供实际的SQL查询。如果是这样,那么https://dev.mysql.com/doc/refman/5.7/en/explain.html展示了如何使用EXPLAIN,并且MySQL Workbench中的某处是Visual Explain。 –