mysql - 在这种情况下，使用组合查询还是两个单独的查询更好？

Question

我合并了这两个查询:

SELECT  `ad_general`.`id`, `ad_general`.`status`, `ad_general`.`category`, `ad_general`.`type`, `ad_general`.`specification`, `ad_general`.`m2`, `ad_general`.`price`, `ad_general`.`title`, `ad_general`.`city`, `ad_general`.`parabirimi`, `ad_general`.`tarih`, `kgsim_districts`.`isim` as district, `kgsim_cities`.`isim` as city, (
 SELECT `adpictures`.`picturelink`
 FROM `adpictures`
 WHERE `adpictures`.`adid` = `ad_general`.`id` LIMIT 1
 ) AS picture
FROM (`ad_general`)
LEFT JOIN `kgsim_cities` ON `kgsim_cities`.`id` = `ad_general`.`city`
LEFT JOIN `kgsim_districts` ON `kgsim_districts`.`id` = `ad_general`.`district`
WHERE `ad_general`.`city` =  '708'
AND `ad_general`.`status` =  '1'
AND `ad_general`.`category` =  '1'
AND `ad_general`.`type` =  '3'
ORDER BY `ad_general`.`id` desc
LIMIT 15 

SELECT  count(`ad_general`.`id`) as sayi
FROM (`ad_general`)
WHERE `city` =  '708'
AND `status` =  '1'
AND `category` =  '1'
AND `type` =  '3'

在该查询中:

SELECT  `ad_general`.`id`, (
 SELECT  count(`ad_general`.`id`) as sayi
 FROM (`ad_general`)
 WHERE `city` = 708 AND `status` = 1 AND `category` = 1 AND `type` = 3 ) AS sayi, `ad_general`.`status`, `ad_general`.`category`, `ad_general`.`type`, `ad_general`.`specification`, `ad_general`.`m2`, `ad_general`.`price`, `ad_general`.`title`, `ad_general`.`city`, `ad_general`.`parabirimi`, `ad_general`.`tarih`, `kgsim_districts`.`isim` as district, `kgsim_cities`.`isim` as city, (
 SELECT `adpictures`.`picturelink`
 FROM `adpictures`
 WHERE `adpictures`.`adid` = `ad_general`.`id` LIMIT 1
 ) AS picture
FROM (`ad_general`)
LEFT JOIN `kgsim_cities` ON `kgsim_cities`.`id` = `ad_general`.`city`
LEFT JOIN `kgsim_districts` ON `kgsim_districts`.`id` = `ad_general`.`district`
WHERE `ad_general`.`city` =  '708'
AND `ad_general`.`status` =  '1'
AND `ad_general`.`category` =  '1'
AND `ad_general`.`type` =  '3'
ORDER BY `ad_general`.`id` desc
LIMIT 15 

我想知道哪一种是最好的方法，为什么？因为这让我对每一行都有“sayi”结果感到有点困惑。

Answer 1

我会使用mysql explain command对这两个查询进行操作，并根据结果调整数据库。

我倾向于“一体化”方法，因为它一次性为 mysql 提供了更多信息，因此它可以更好地决定如何运行搜索。

然而，变数实在太多。通常运行解释然后调整表或索引并再次运行解释可以提供最佳性能恕我直言。