MySQL order by not ordering

I got items rated by both ratings, and use an ORDER BY key to order them by positive, but with an example table:

//(item)-(pos)-(neg):
开发者_运维百科item 1 - 50 - 200
item 2 - 24 - 0
item 3 - 15 - 12

But item 1 is really less rating than the others, how would I fix this?

You can do ORDER BY pos - neg DESC but it will be slow because it cannot use an index.

You can denormalize your database by explicitly storing the value of pos - neg in the table so that you can index it to improve performance. Usually denormalization is regarded as a bad thing and should be avoided but if it the improved performance is required then it is an acceptable approach.

You can find the net score by subtracting neg from pos, then order by that net score. Calculate it, then give it an alias, we'll call it net:

mysql> SELECT item, pos, neg, (pos - neg) AS net
    -> FROM table
    -> ORDER BY net DESC;
+------+-----+-----+------+
| item | pos | neg | net  |
+------+-----+-----+------+
|    2 |  24 |   0 |   24 |
|    3 |  15 |  12 |    3 |
|    1 |  50 | 200 | -150 |
+------+-----+-----+------+
3 rows in set (0.00 sec)

Yes you can use math in order.

ORDER BY pos-neg DESC

BUT make sure pos and neg are not UNSIGNED! Because when they try to go negative you will end up with biggest result ever ;)