我有4个表格：地点，品牌，类别，位置。
关系是“属于品牌的地方”和“具有类别和位置的很多地方”。
我想获取搜索结果中具有特定类别和位置的地点，但每个品牌仅显示1个地点。
表格信息
places表包含约10万多行
place_category数据透视表包含650k +行，其中place_category.place_id和places.brand_id列已被索引
place_locations数据透视表包含约550k以上的行，其中place_location.place_id和place_location.location_id列已索引
我到目前为止得到的查询
Place::join('place_location',function ($join) use ($city) {
$join->on('place_location.place_id','=','places.id')
->where('place_location.location_id',$city->id);
})
->join('place_category',function ($join) {
$join->on('place_category.place_id','places.id')
->where('place_category.category_id',$category->id);
})
->groupBy('places.brand_id')
->take(5)
->get();
groupBy导致查询时间变慢，约为2秒。
解释结果看起来像这样
id | select_type | table | possible_key | key | key_len | ref | rows | Extra
1 | SIMPLE | places | PRIMARY | brand_id | 4 | NULL | 50 | Using where
1 | SIMPLE | place_location | place_id,place_location | place_location | 4 | const,db.places.id | 1 | Using index
1 | SIMPLE | place_category | place_category | place_category | 4 | db.places.id,const | 1 | Using where; Using index
原始MySQL查询如下所示
select
`places`.`id`,`places`.`name`,`places`.`display`,`places`.`status_a`,`places`.`status_b`,`places`.`brand_id`,`places`.`address`
from `places`
inner join `place_location`
on `place_location`.`place_id` = `places`.`id`
and `place_location`.`location_id` = 4047
inner join `place_category`
on `place_category`.`place_id` = `places`.`id`
and `place_category`.`category_id` = 102
where
`places`.`status_a` != 1
and `status_b` = 2
and `display` >= 5
group by `places`.`brand_id`
limit 4
显示创建表如下所示
CREATE TABLE `places` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`user_id` int(11) unsigned DEFAULT NULL,`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`desc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,`city_id` int(11) unsigned NOT NULL DEFAULT '102',`state_id` int(11) unsigned NOT NULL DEFAULT '34',`location_id` int(11) unsigned NOT NULL DEFAULT '15',`landmark_id` int(10) unsigned NOT NULL DEFAULT '1',`postcode` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,`country_id` int(4) unsigned NOT NULL,`lat` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,`long` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,`phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,`sec_phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,`third_phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,`fourth_phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,`brand_id` int(10) NOT NULL DEFAULT '1',`display` int(11) NOT NULL DEFAULT '0',`view` int(10) unsigned NOT NULL DEFAULT '0',`status_b` tinyint(3) unsigned NOT NULL DEFAULT '2',`status_a` tinyint(4) NOT NULL DEFAULT '2',`company_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,`slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`lock_status_id` tinyint(3) unsigned DEFAULT '1',`created_at` timestamp NULL DEFAULT NULL,`updated_at` timestamp NULL DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `slug` (`slug`),KEY `city_id` (`city_id`),KEY `location_id` (`location_id`),KEY `user_id` (`user_id`),KEY `landmark_id` (`landmark_id`),KEY `name` (`name`),KEY `brand_id` (`brand_id`),KEY `groupby_brandid` (`status_b`,`display`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=116070 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `place_location` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`location_id` int(10) NOT NULL,`place_id` int(10) NOT NULL,KEY `place_location` (`place_id`,`location_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=564259 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `place_category` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`category_id` int(11) unsigned NOT NULL,`place_id` int(11) unsigned NOT NULL,`branch_id` int(11) unsigned NOT NULL,KEY `place_id` (`place_id`),KEY `place_category` (`category_id`,`place_id`)
) ENGINE=InnoDB AUTO_INCREMENT=905384 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
任何想法如何改善查询？错误的索引？或查询错误？