Is efficient the following SQL query?

Question

Exist a better way to do what the following SQL query does? I have the feeling that table1 will be searched twice and may be that can be avoided with some trick and increase the efficient of the query, but I just can't figure out how ;( Here is the query (in MySQL):

SELECT a, SUM(count)
FROM table1
GROUP BY a
HAVING SUM(count) = (SELECT SUM(count) as total FROM table1 GROUP BY a ORDER BY total DESC LIMIT 1)

The goal is return the number(s) with the major accumulate, with its accumulate.

being table1 a two field table like:

a,count
1,10
1,30
1,0
2,1
2,100
2,4
3,10
4,50
4,55

The result with that data sample is:

2,105
4,105

Thanks in advance.

Why did someone downs vote this question? I think he just needs an advice to optimize his query — Doan Cuong, Apr 16 '13 at 03:09

score 3 · Answer 1 · answered Apr 16 '13 at 02:51

3

SELECT a, total FROM 
(SELECT a AS a, SUM(COUNT) AS total
FROM table1
GROUP BY a) AS xyz 
HAVING total = MAX(total)

Hope this will work for you

answered Apr 16 '13 at 02:51

Doan Cuong

2,594
4
22
39

Uff, I just test it and return 'Empty set', removing the HAVING part return a set. Seem to me that MySQL don't like the total = MAX(total) trick. Any idea? Thanks again. – Apr 16 '13 at 03:57
In fact, seem that any a query so simple like `SELECT a, count FROM table1 HAVING count = MAX(count)` don't work. That is the problem to 'solve', if this worked, may be a even better solution could be: `SELECT a, SUM(count) as total FROM table1 HAVING total = MAX(total)` – Apr 16 '13 at 04:04
So, did you try with `SELECT a, SUM(count) as total FROM table1 HAVING total = MAX(total)`? I think it will work fine – Doan Cuong Apr 16 '13 at 04:29
Yes, I try, and not work. If work, the query I put in the above comment could be even faster that what you propose, but both query are worthless cos doesn't work. Thanks anyway. – Apr 16 '13 at 04:42

score 0 · Answer 2 · answered Apr 16 '13 at 02:49

0

I think you could probably do it by moving your HAVING sub-select query into its on prequery. Since it will always include a single row, you won't require any "JOIN", and it does not have to keep recomputing the COUNT(*) every time the HAVING is applied. Do it once, then the rest

SELECT 
      a, 
      SUM(count)
   FROM 
      table1,
      ( SELECT SUM(count) as total 
           FROM table1 
           GROUP BY a 
           ORDER BY total DESC 
           LIMIT 1 ) PreQuery
   GROUP BY 
      a
   HAVING 
      SUM(count) = PreQuery.Total

answered Apr 16 '13 at 02:49

DRapp

47,638
12
72
142

Thanks for the tip, but still seem to me that the table need to be searched twice, that can't be avoided? – Apr 16 '13 at 03:35
Wao, 17.1K, you must be good, pls, take a look at the comment of the other questions if you have time ;) May be you can help us. Thanks. – Apr 16 '13 at 04:05

score 0 · Accepted Answer · edited May 23 '17 at 12:04

This sub-query is executed only once, and you don't have to bother with creating any pre-query as other answers may suggest (although doing so this is still correct, just not needed). Database engine will realise, that the sub-query is not using any variable dependent on the other part of the query. You can use EXPLAIN to see how the query is executed.

More on the topic in this answer: https://stackoverflow.com/a/658954/1821029

score 0 · Answer 4 · answered Apr 16 '13 at 10:34

0

This query return one row with two columns:

1- a list of comma separated values of "a" column, which have the biggest "Total"

2- and the biggest Total value

select group_concat(a), Total
from
(select a, sum(count) as Total
from table1
group by a) OnTableQuery
group by Total
order by Total desc
limit 1

Note that it queries table1 just one time. The query was already tested.

answered Apr 16 '13 at 10:34

Delmo

2,188
2
20
29

This is similar to the @DRapp query structure and due to have the same problems: 1- With some data sample (those where 'a' fields are unique or repeat little), is almost equivalent to do two query. 2- Is no generalizable to the cases that is needed get more than the total with the 'a', in case that table1 have more fields or exist others fields on other tables related. Beside, specifically this solution lost paginate. Tomasz said something interesting. If that is true, the best query must be very like to what I wrote, due to readability, I just want test first before assign her reply. Thanks. – Apr 16 '13 at 14:28

Is efficient the following SQL query?

4 Answers4