Skip to content

Instantly share code, notes, and snippets.

@spetrunia
Created November 30, 2018 16:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save spetrunia/5445ef362aa26207ccbf83c093d7135b to your computer and use it in GitHub Desktop.
Save spetrunia/5445ef362aa26207ccbf83c093d7135b to your computer and use it in GitHub Desktop.
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