1

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?

3
  • 2
    You need an index on (points, user_id) for the specific query to be faster. Nov 14, 2015 at 7:41
  • The mul in the key column of your describe says the points col to be already indexed. Add full create table statement please.
    – jkavalik
    May 15, 2016 at 14:03
  • 1
    Otherwise you can just add the index and check if the IO usage gets significantly higher. I doubt that but you should verify anyway. How big is the table? How much RAM does your server have?
    – jkavalik
    May 15, 2016 at 14:05

1 Answer 1

-2
| 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
1
  • 1
    "sending data" is unfortunately named thread state which covers fetching rows from the storage.
    – jkavalik
    May 15, 2016 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.