How can I optimize my query (rank query)?
For the last two days, I have been asking questions on rank queries in Mysql. So far, I have working queries for
- query all the rows from a table and order by their rank.
- query ONLY one row with its rank
Here is a link for my question from last night
How to get a row rank?
As you might notice, btilly's query is pretty fast.
Here is a query for getting ONLY one row with its rank that I made based on btilly's query.
set @points = -1;
set @num = 0;
select * from (
SELECT id
, points
, @num := if(@points = points, @num, @num + 1) as point_rank
, @points := points as dummy
FROM points
ORDER BY points desc, id asc
) as test where test.id = 3
the above query is using subquery..so..I am worrying about the performance.
are there any other faster queries that I can use?
Table po开发者_如何转开发ints
id points
1 50
2 50
3 40
4 30
5 30
6 20
Don't get into a panic about subqueries. Subqueries aren't always slow - only in some situations. The problem with your query is that it requires a full scan.
Here's an alternative that should be faster:
SELECT COUNT(DISTINCT points) + 1
FROM points
WHERE points > (SELECT points FROM points WHERE id = 3)
Add an index on id
(I'm guessing that you probably you want a primary key here) and another index on points
to make this query perform efficiently.
精彩评论