gistfile1.txt

Trying an example from https://www.fromdual.com/mariadb-indexing-of-null-values . What if we replace the SQL NULL value with
some non-NULL value? I used 'not-a-null-value' below.  The results are the same as with SQL NULL.

MariaDB [test2]> create table ten(a int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test2]> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

MariaDB [test2]> 
MariaDB [test2]> create table one_k(a int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test2]> insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Duplicates: 0  Warnings: 0

MariaDB [test2]> 
MariaDB [test2]> CREATE TABLE null_test (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> , data VARCHAR(32) DEFAULT NULL
    -> , ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test2]> 
MariaDB [test2]> insert into null_test select NULL,  'Some data to show if null works', NULL from one_k A, one_k B;
Query OK, 1000000 rows affected (4.57 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

MariaDB [test2]> 
MariaDB [test2]> 
MariaDB [test2]> UPDATE null_test SET data = 'not-a-null-value' WHERE ID % 1000 = 0;
Query OK, 1000 rows affected (1.08 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

MariaDB [test2]> ALTER TABLE null_test ADD INDEX (data);
Query OK, 0 rows affected (7.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test2]> ANALYZE TABLE null_test;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| test2.null_test | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.01 sec)

MariaDB [test2]> 
MariaDB [test2]> EXPLAIN EXTENDED
    -> SELECT max(ts) FROM null_test WHERE data ='not-a-null-value';
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+
|    1 | SIMPLE      | null_test | ref  | data          | data | 35      | const | 1000 |   100.00 | Using index condition |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test2]> 
MariaDB [test2]> SELECT max(ts) FROM null_test force index(data) WHERE data ='not-a-null-value';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test force index(data) WHERE data ='not-a-null-value';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.01 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test force index(data) WHERE data ='not-a-null-value';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test2]> 
MariaDB [test2]> SELECT max(ts) FROM null_test ignore index(data) WHERE data='not-a-null-value';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.15 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test ignore index(data) WHERE data='not-a-null-value';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.14 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test ignore index(data) WHERE data='not-a-null-value';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.14 sec)

MariaDB [test2]> 
MariaDB [test2]> 
MariaDB [test2]> EXPLAIN EXTENDED
    -> SELECT max(ts) FROM null_test WHERE data = 'Some data to show if null works';
+------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref   | rows   | filtered | Extra                 |
+------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
|    1 | SIMPLE      | null_test | ref  | data          | data | 35      | const | 498094 |   100.00 | Using index condition |
+------+-------------+-----------+------+---------------+------+---------+-------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test2]> 
MariaDB [test2]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (1.06 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (1.00 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.96 sec)

MariaDB [test2]> 
MariaDB [test2]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.45 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.45 sec)

MariaDB [test2]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works';
+---------------------+
| max(ts)             |
+---------------------+
| 2018-11-30 14:30:55 |
+---------------------+
1 row in set (0.44 sec)

MariaDB [test2]>