CODEWITHSUNDEEP
mysqlsqlquery-optimizationsubquery
mirza
mirza

Reputation: 5793

How that subquery duplicating results ? Is it sending same request for each row in results?

I have two seperate queries:

SELECT  `ad_general`.`id`
FROM (`ad_general`)
WHERE `ad_general`.`city` =  '708'
ORDER BY `ad_general`.`id` desc
LIMIT 15 

SELECT  count(`ad_general`.`id`) as count
FROM (`ad_general`)
WHERE `city` =  '708'

I have combined these two queries for avoiding sending multiple requests to mysql results for performance gain.

SELECT  `ad_general`.`id`, (
 SELECT  count(`ad_general`.`id`) as count
 FROM (`ad_general`)
 WHERE `city` = 708 ) AS count,
FROM (`ad_general`)
WHERE `ad_general`.`city` =  '708'
ORDER BY `ad_general`.`id` desc
LIMIT 15

In first approach the "count" column has only one row and clearly there is only one request for count column.

But in combined query "count" column has 15 same rows.

I was wondering how mysql duplicating that "count" column in that subquery. If it's sending same count request for each row (15 times in that case) it's not wise to use a combined query in that case.

Upvotes: 1

Views: 75

Answers (1)

Borealid
Borealid

Reputation: 98559

In the first query, you are asking the database server for two pieces of information. First, the first 15 rows in the table. Second, the total number of rows in the table.

In the second query, you are asking it for sixteen. You want the first 15 rows of the table, and for each row, you want the total number of rows.

The performance impact of this depends on the database particulars. But no, it's not wise: just make two queries. This is what ORMs such as Django do to handle pagination.

Premature optimization is generally a bad idea. If you aren't sure that sending the COUNT queries is hurting you (and it probably isn't), don't add complexity to "fix" what you don't know is a problem. If you are sure it's hurting you, then measure the performance of both means to see which one is superior. The proper performance optimization (almost certainly not necessary) is to keep a denormalized count of the rows in a ready cache.

Upvotes: 1

Related Questions