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

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，并根据结果调整DB。

我站在‘all in one’的方法一边，因为它给mysql提供了更多的信息，这样它就可以更好地决定如何运行搜索。

然而，有这么多的变量。通常，运行explain，然后调整表或索引，然后再次运行explain，可以获得最佳性能。

Answer 2

如果您只考虑查询时间，第一个选项可能会更快。然而，两个独立的查询占用了更多的服务器，如果您的连接时间很长，查询将会很慢。

如果您正确地使用了索引和缓存表，那么第二种方法是更好的方法，特别是当您有大量查询或连接时。