Optimizing SELECT id, SUM(x) xsum GROUP BY id ORDER BY xsum
i have a table with 25+ million rows and i have a query that needs to the top n ids
for varying criteria, but he basic problem is optimizing something like:
SELECT id, SUM(x) xsum
WHERE ....
GROUP BY id
ORDER BY xsum
LIMIT n
i have a clustered index on id and the fields in the where clause
without the ORDER BY, this is very fast as due to id being the first index in the cluster (i think), the problem is when the ORDER BY is added the result is quite large (1-2 million) and ordering such a set takes ~10-15 seconds
ive tried partitioning the query by turning id into MD5(id) and then doing
SELECT id, SUM(x) xsum FROM (
SELECT id, SUM(x) xsum
WHERE ....
AND id LIKE '0%'
GROUP BY id
UNION ALL
SELECT id, SUM(x) xsum
WHERE ....
AND id LIKE '1%'
GROUP BY id
UNION ALL
.
.
.
)
ORDER BY xsum
LIMIT n
but though each query is the union is much faster, once i union all 16 (0-9a-f) it still takes about the same time
is there anyway to optimize such a query given i only ned the top n?
Subject
Views
Written By
Posted
Optimizing SELECT id, SUM(x) xsum GROUP BY id ORDER BY xsum
6170
January 27, 2008 11:16AM
2083
January 27, 2008 04:46PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.