CODEWITHSUNDEEP
mysqloptimizationsubquery
mirza
mirza

Reputation: 5793

is it better to use a combined query or two seperate queries in that situation?

I have combined these two queries:

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'

In that query:

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

I was wondering which one is the best approach and why ? Because it makes me a little bit confused to having "sayi" result in every row.

Upvotes: 0

Views: 72

Answers (2)

JuSchz
JuSchz

Reputation: 1198

First option may be faster if your consider only query time. However, two separated queries occupy the server more, and if you connection time is big, query will be slow.

If you correctly uses indexes and cache tables, the second is the better approach, specially if you have a lot of queries or connections.

Upvotes: 1

matiu
matiu

Reputation: 7725

I'd use the mysql explain command on both queries, and tweak the DB depending on the results.

I'd side with 'the all in one' approach as it gives mysql more information in one go, so it can better decide how to run the search.

However there are so many variables. Usually running explain then tweaking the tables or indexes and running explain again gives the best performance IMHO.

Upvotes: 1

Related Questions