EXPLAIN minor wishlist

While we always want better performance and more and larger features for MySQL, those cannot just “magically appear” from one version to another, requiring deep architecture changes and lots of lines of code. However, there are sometimes smaller features and fixes that could be implemented by an intern or an external contributor, mainly at SQL layer, and that could make the MySQL ecosystem friendlier to newbies and non-experts. Making a piece of software easier to use is sometimes overlooked, but it is incredibly important -not everybody using MySQL is a DBA, and the more people adopting it, more people will be able to live from it, both upstream and as third party providers.

Here it is my own personal list of fixes for EXPLAIN messages. If you are an experienced MySQL user you are probably aware of their meaning, but that doesn’t solve the problem for beginners. The reason why I am writing a blog post is to gather opinions on whether they seem important to you or not, and if my way of solving them seems reasonable so that we can submit them as feature requests.

EXPLAIN messages

As a MySQL instructor, the following case happens a lot with new students. You start with a command like this:

mysql> EXPLAIN SELECT b, c FROM test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: b_c
      key_len: 10
          ref: NULL
         rows: 4
        Extra: Using index
1 row in set (0.00 sec)

So, “Using index” means that an index is being used, right? No, in this case, the type: index is telling us that it is using an index for scanning or accessing the rows (because it is not a type: ALL– although we could get a full row scan and using the index for ordering or grouping them). The Extra: Using index indicates that the index is also used for retrieving the data, without actually needing to read the whole row. This is, as far as I know, commonly referred as Covering index. And that is exactly what I would like to see:

mysql> EXPLAIN SELECT b, c FROM test\G -- edited output
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: b_c
      key_len: 10
          ref: NULL
         rows: 4
        Extra: Using covering index
1 row in set (0.00 sec)

or maybe:

        Extra: Covering index

Another common misunderstanding: Using filesort:

mysql> EXPLAIN SELECT * FROM test ORDER BY length(b)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set (0.00 sec)

At this level, I do not care if I am using filesort as an algorithm, and -if I am correct- since 5.6 can also use a priority queue for the sorting algorithm if the number of items is small. Additionally, the “file” in the filesort word can lead to confusion that this requires a temporary table on disk. I do not have a perfect alternative (please provide feedback), but maybe something like the following would be clearer:

mysql> EXPLAIN SELECT * FROM test ORDER BY length(b)\G  -- edited output
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Additional sort phase
1 row in set (0.00 sec)

or maybe:

        Extra: Not using index for order-by

Another example would be:

mysql> EXPLAIN SELECT a FROM test WHERE b > 3 and c = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: b_c
          key: b_c
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

I understand that the developers didn’t want to confuse us with NDB’s pushed condition, but this output is quite misleading, too. It literally means that “the index condition is being used”, instead of “ICP is being used”. What about:

mysql> EXPLAIN SELECT a FROM test WHERE b > 3 and c = 3\G -- edited output
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: b_c
          key: b_c
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index condition pushdown
1 row in set (0.00 sec)

There are many other expressions, but those are the most annoying to me in terms of students’ confusion.

Would you agree with me? Would these changes break applications that may parse EXPLAIN output? What other small things would you change in MySQL output or error messages? I would specially would like to hear from MySQL beginners and people coming from other databases, as the more we have used to it, the more we get accustomed to MySQLisms.