This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Trying an example from https://www.fromdual.com/mariadb-indexing-of-null-values, part #2. | |
With MyRocks storage engine, the best query plan is picked automatically. This proves the problem is in innodb, not the optimizer. | |
MariaDB [test3]> set rocksdb_max_row_locks=1024*1024*500; | |
Query OK, 0 rows affected (0.00 sec) | |
MariaDB [test3]> set storage_engine=rocksdb; | |
Query OK, 0 rows affected (0.00 sec) | |
MariaDB [test3]> create table ten(a int); | |
Query OK, 0 rows affected (0.01 sec) | |
MariaDB [test3]> 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 [test3]> | |
MariaDB [test3]> create table one_k(a int); | |
Query OK, 0 rows affected (0.02 sec) | |
MariaDB [test3]> 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 [test3]> | |
MariaDB [test3]> 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.01 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> 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 (15.20 sec) | |
Records: 1000000 Duplicates: 0 Warnings: 0 | |
MariaDB [test3]> | |
MariaDB [test3]> | |
MariaDB [test3]> UPDATE null_test SET data = NULL WHERE ID % 1000 = 0; | |
Query OK, 1000 rows affected (5.77 sec) | |
Rows matched: 1000 Changed: 1000 Warnings: 0 | |
MariaDB [test3]> ALTER TABLE null_test ADD INDEX (data); | |
Query OK, 0 rows affected (4.51 sec) | |
Records: 0 Duplicates: 0 Warnings: 0 | |
MariaDB [test3]> ANALYZE TABLE null_test; | |
+-----------------+---------+----------+----------+ | |
| Table | Op | Msg_type | Msg_text | | |
+-----------------+---------+----------+----------+ | |
| test3.null_test | analyze | status | OK | | |
+-----------------+---------+----------+----------+ | |
1 row in set (0.00 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> EXPLAIN EXTENDED | |
-> SELECT max(ts) FROM null_test WHERE data IS NULL; | |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | |
| 1 | SIMPLE | null_test | ref | data | data | 35 | const | 468 | 100.00 | Using index condition | | |
+------+-------------+-----------+------+---------------+------+---------+-------+------+----------+-----------------------+ | |
1 row in set, 1 warning (0.00 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data IS NULL; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (0.01 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data IS NULL; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (0.01 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data IS NULL; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (0.01 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data IS NULL; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (0.56 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data IS NULL; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (0.49 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data IS NULL; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (0.49 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> 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 | ALL | data | NULL | NULL | NULL | 1001000 | 99.00 | Using where | | |
+------+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+ | |
1 row in set, 1 warning (0.00 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works'; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (5.09 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works'; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (8.65 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test force index(data) WHERE data = 'Some data to show if null works'; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (4.98 sec) | |
MariaDB [test3]> | |
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works'; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (1.42 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works'; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (1.42 sec) | |
MariaDB [test3]> SELECT max(ts) FROM null_test ignore index(data) WHERE data = 'Some data to show if null works'; | |
+---------------------+ | |
| max(ts) | | |
+---------------------+ | |
| 2018-11-30 16:33:37 | | |
+---------------------+ | |
1 row in set (1.42 sec) | |
MariaDB [test3]> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment