ame    |            2 | last_name   
 | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
       |
+-------------------+------------+---------------+--------------+--------------
+-----------+-------------+----------+--------+------+------------+---------+--
------------+

SELECT on a primary key:

EXPLAIN SELECT * FROM employees_example WHERE id=1;
+------+-------------+-------------------+-------+---------------+---------+---
-----+-------+------+-------+
| id   | select_type | table             | type  | possible_keys | key     |
key_len | ref   | rows | Extra |
+------+-------------+-------------------+-------+---------------+---------+---
-----+-------+------+-------+
|    1 | SIMPLE      | employees_example | const | PRIMARY       | PRIMARY | 4
   | const |    1 |       |
+------+-------------+-------------------+-------+---------------+---------+---
-----+-------+------+-------+

The type is const, which means that only one possible result could be
returned. Now, returning the same record but searching by their phone number:

EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492';
+------+-------------+-------------------+------+---------------+------+-------
-+------+------+-------------+
| id   | select_type | table             | type | possible_keys | key  |
key_len | ref  | rows | Extra       |
+------+-------------+-------------------+------+---------------+------+-------
-+------+------+-------------+
|    1 | SIMPLE      | employees_example | ALL  | NULL          | NULL | NULL 
 | NULL |    6 | Using where |
+------+-------------+-------------------+------+---------------+------+-------
-+------+------+-------------+

Here, the type is All, which means no index could be used. Looking at the rows
count, a full table scan (all six rows) had to be performed in order to
retrieve the record. If it's a requirement to search by phone number, an index
will have to be created.

SHOW EXPLAIN example:

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+-
-------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  |
rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+-
-------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL |
1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+-
-------+-------------+
1 row in set, 1 warning (0.00 sec)

Example of ref_or_null Optimization
-----------------------------------

SELECT * FROM table_name
 WHERE key_column=expr OR key_column IS NULL;

ref_or_null is something that often happens when you use subqueries with NOT
IN as then one has to do an extra check for NULL values if the first value
didn't have a matching row.

URL: https://mariadb.com/kb/en/explain/