Making Increment Counter And Multiple SELECT Statement More Efficient

Question

What is a more efficient way of performing the following MySQL statement? I'm trying to make my queries as efficient as possible. Thanks in advance!!

MySQL Query:

SELECT nick as viewer,
CONVERT(rank, UNSIGNED) as rank, 
CONVERT(FLOOR(amount), UNSIGNED) as amount
FROM
(SELECT @rank:=@rank+1 AS rank, 
nick, amount FROM points, 
(SELECT @rank := 0) t 
ORDER BY amount DESC) as t 
LIMIT 50;

Explain Output:

mysql> EXPLAIN SELECT nick as viewer, CONVERT(rank, UNSIGNED) as rank, CONVERT(FLOOR(amount), UNSIGNED) as amount FROM (SELECT @rank:=@rank+1 AS rank, nick, amount FROM points, (SELECT @rank := 0) t ORDER BY amount DESC) as t LIMIT 50;
+----+-------------+------------+--------+---------------+------+---------+------+--------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+--------+----------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL | 102298 |                |
|  2 | DERIVED     | <derived3> | system | NULL          | NULL | NULL    | NULL |      1 | Using filesort |
|  2 | DERIVED     | points     | ALL    | NULL          | NULL | NULL    | NULL | 103890 |                |
|  3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL |   NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+--------+----------------+
4 rows in set (3.01 sec)

Table description:

mysql> describe points;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| nick   | varchar(255)  | NO   | PRI | NULL    |       |
| amount | decimal(10,4) | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

Example output:

+--------------------------+------+--------+
| viewer                   | rank | amount |
+--------------------------+------+--------+
| dakuda                   |    1 |    768 |
| resurrex                 |    2 |    575 |
| stgchrist                |    3 |    502 |
| artisun001               |    4 |    446 |
| xjenniewasherex          |    5 |    366 |
....
50 rows in set (3.23 sec)

Answer 1

This ought to be marginally faster. I tried it by filling a table such as yours with 2^17 random records (plus a sample from yours):

SELECT nick AS viewer, @counter := @counter + 1 AS rank, FLOOR(amount) AS amount
FROM points
JOIN (SELECT @counter := 0) AS init
ORDER BY amount DESC
LIMIT 50

and got,

+----------------------------------+------+--------+
| viewer                           | rank | amount |
+----------------------------------+------+--------+
| dakuda                           |    1 |    768 |
| 9c91a2e708f61d7d790c00e289509893 |    2 |    699 |
| 114372e32506acb21a389463f761526c |    3 |    699 |
| 522f93c197550a532727ae894b164743 |    4 |    699 |
| 55f272eecfaff0dbd3d55a2b525d772f |    5 |    699 |
...
| 5f5c9b1dd560b37307eff23fe1b3e58f |   50 |    699 |
+----------------------------------+------+--------+
50 rows in set (0.02 sec)

The difference lies in the ranking method - you are actually selecting all rows from points, then LIMITing them in the outer query. You can also modify your version like this - you bring the LIMIT inside -

SELECT nick as viewer, CONVERT(rank, UNSIGNED) as rank,  CONVERT(FLOOR(amount),
UNSIGNED) as amount FROM (SELECT @rank:=@rank+1 AS rank,
nick, amount FROM points,  (SELECT @rank := 0) t
ORDER BY amount DESC LIMIT 50) as t;

and obtain a marked performance increase.

Tricks

If you have to display repeatedly this information, and you can estimate the maximum variation that any given score may have undergone in the time between, then you can dramatically improve performances by saving the last lowest score.

Suppose that no one player can garner more than 20 points per day and that the last lowest score displayed in the top fifty was 500, and this was three days ago.

If that is true, the worst that may have happened is that no player in the top fifty scored a single point and some other players scored the full 20 points per day, achieving 60 extra points. But if they were lower than 440, they still have no chances of making top fifty, so you can add an INDEX on amount as well as a WHERE condition that can shave a large percentage of data that needn't be examined:

CREATE INDEX points_amount_ndx ON points(amount);

SELECT
    nick AS viewer,
    @counter := @counter + 1 AS rank,
    FLOOR(amount) AS amount
FROM points, (SELECT @counter := 0) AS init
WHERE amount > 440
ORDER BY amount DESC
LIMIT 50

You then save the fiftieth value of amount and the current timestamp somewhere for the next time.

For heavily hit web sites, if chances are significant that many more queries will come in between point updates, you can also use SQL_CACHE:

SELECT SQL_CACHE
    nick AS viewer,
    @counter := @counter + 1 AS rank,
    FLOOR(amount) AS amount
FROM points, (SELECT @counter := 0) AS init
WHERE amount > 440
ORDER BY amount DESC
LIMIT 50

...this drops my query times to effectively zero. After each point update, the cache is invalidated and the query is recalculated... once.