title: Making Increment Counter And Multiple SELECT Statement More Efficient
body: <p>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!!</p>
  
  <p>MySQL Query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Explain Output:</p>
  
  <pre><code>mysql&gt; 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     | &lt;derived2&gt; | ALL    | NULL          | NULL | NULL    | NULL | 102298 |                |
  |  2 | DERIVED     | &lt;derived3&gt; | 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)
  </code></pre>
  
  <p>Table description:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>Example output:</p>
  
  <pre><code>+--------------------------+------+--------+
  | 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)
  </code></pre>