Speeding up a simple select query on Mariadb

Question

I have a 100k rows table with the following structure:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)          | NO   | UNI | NULL    |                |
| success  | int(10) unsigned | NO   |     | NULL    |                |
| fail     | int(10) unsigned | NO   |     | NULL    |                |
| points   | int(11)          | NO   | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

This table holds users' scores in a game. All fields' values other than id's and user_id's are constantly changing on every user event.

A simple select used for displaying the top scoring users on this table runs in 0.3s on production.

MariaDB [db]> select user_id, points from state where points > 100000 ;
...
119 rows in set (0.31 sec)
MariaDB [db]>show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000090 |
| checking permissions | 0.000018 |
| Opening tables       | 0.000030 |
| After opening tables | 0.000014 |
| System lock          | 0.000015 |
| Table lock           | 0.000013 |
| After table lock     | 0.000016 |
| init                 | 0.000027 |
| optimizing           | 0.000021 |
| statistics           | 0.000029 |
| preparing            | 0.000034 |
| executing            | 0.000012 |
| Sending data         | 0.307494 |
| end                  | 0.000041 |
| query end            | 0.000018 |
| closing tables       | 0.000036 |
| freeing items        | 0.000015 |
| updating status      | 0.000038 |
| logging slow query   | 0.000068 |
| cleaning up          | 0.000034 |
+----------------------+----------+

For the points column is ever changing, I did not want to create an index for it. And this behaviour is expected. But a full select, without a where condition runs for 0.4s either.

MariaDB [db]> select user_id, points from state;
...
126851 rows in set (0.40 sec)

If I create an index for the points column, it will most probably result in great amount of IO operations on the server, but It will speed up the top scores list. If I filter and sort the list in server side code, I can speed up the list, but this time mariadb does not provide the result in a shorter time.

How can I speed up this simple select query?

Answer 1

| Sending data         | 0.307494 |

Most of your time is spend on transporting results from server to your client.

• use compression (MYSQLI_CLIENT_COMPRESS)
• get faster connection
• select less data