1

I'm trying to optimise my MySQL queries using EXPLAIN. Here is a very simply example of a query:

SELECT ID, Date, SenderID, Message FROM messages WHERE ReceiverID = '1000' ORDER BY ID DESC LIMIT 25;

When I view the EXPLAIN it shows:

+----+-------------+-----------+------+---------------+------------+---------+-------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | Extra                       |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | messages  | ref  | ReceiverID    | ReceiverID | 5       | const | 34   | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-----------------------------+

The number of results is 15. I have created an index for ReceiverID so I don't understand why 'rows' in the EXPLAIN is show 34? I also dont under why it's using filesort. This query isnt very slow but other queries with larger results tend to be slow and the 'row' figure seems to be almost double what I expect it to be...

Its probably something very straight forward but I must be missing something?

Marvin
  • 13,325
  • 3
  • 51
  • 57
Carlos88
  • 21
  • 1

1 Answers1

0

The meaning of filesort is that the sort cannot be done from index.

For more info read here: https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/.

I suggest that the index should span ReceiverID and id.

Then the sort can be done from the index alone.

The number of rows is just an approximation. you cannot know the number of rows till you execute the query. For more info look here: Why the rows returns by "explain" is not equal to count()?

Community
  • 1
  • 1
David Michael Gang
  • 7,107
  • 8
  • 53
  • 98
  • Hi David, thanks for the advice. I thought having ID as primary key would do it but I guess I need to have ReceiveID and ID within the same index. The bigger concern was the number of rows it had to inspect. Seems like far too much but I will check out that link. Thanks – Carlos88 Feb 29 '16 at 12:46