Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.