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