Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ...
- WHERE
- `POP1` IS NOT NULL
- && `VT`='ABC'
- && (`SOURCE`='HOME')
- && (`alt` RLIKE '^[AaCcGgTt]$')
- && (`ref` RLIKE '^[AaCcGgTt]$')
- && (`AA` RLIKE '^[AaCcGgTt]$')
- && (`ref` = `AA` || `alt` = `AA`)
- LIMIT 10 ;
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- | 1 | SIMPLE | myTab | ref | vt_source_pop1_pop2,pop1_vt_source,... | vt_source_pop1_pop2 | 206 | const,const | 20040021 | Using where |
- CREATE TEMPORARY TABLE `myTab` (`notnul` FLOAT, `nul` FLOAT);
- INSERT INTO `myTab` VALUES (1, NULL), (1, 2), (1, NULL), (1, 2), (1, NULL), (1, 2), (1, NULL), (1, 2), (1, NULL), (1, 2), (1, NULL), (1, 2);
- SELECT * FROM `myTab`;
- +--------+------+
- | notnul | nul |
- +--------+------+
- | 1 | NULL |
- | 1 | 2 |
- | 1 | NULL |
- | 1 | 2 |
- | 1 | NULL |
- | 1 | 2 |
- | 1 | NULL |
- | 1 | 2 |
- | 1 | NULL |
- | 1 | 2 |
- | 1 | NULL |
- | 1 | 2 |
- +--------+------+
- CREATE INDEX `notnul_nul` ON `myTab` (`notnul`, `nul`);
- CREATE INDEX `nul_notnul` ON `myTab` (`nul`, `notnul`);
- SHOW INDEX FROM `myTab`;
- +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
- +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- | myTab | 1 | notnul_nul | 1 | notnul | A | 12 | NULL | NULL | YES | BTREE | | |
- | myTab | 1 | notnul_nul | 2 | nul | A | 12 | NULL | NULL | YES | BTREE | | |
- | myTab | 1 | nul_notnul | 1 | nul | A | 12 | NULL | NULL | YES | BTREE | | |
- | myTab | 1 | nul_notnul | 2 | notnul | A | 12 | NULL | NULL | YES | BTREE | | |
- +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- EXPLAIN SELECT * FROM `myTab` WHERE `notnul` IS NOT NULL;
- +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
- | 1 | SIMPLE | myTab | index | notnul_nul | notnul_nul | 10 | NULL | 12 | Using where; Using index |
- +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
- EXPLAIN SELECT * FROM `myTab` WHERE `nul` IS NOT NULL;
- +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
- | 1 | SIMPLE | myTab | range | nul_notnul | nul_notnul | 5 | NULL | 6 | Using where; Using index |
- +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
- EXPLAIN SELECT * FROM `myTab` WHERE `nul` IS NULL && notnul=2;
- +----+-------------+-------+------+-----------------------+------------+---------+-------------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+-----------------------+------------+---------+-------------+------+--------------------------+
- | 1 | SIMPLE | myTab | ref | notnul_nul,nul_notnul | notnul_nul | 10 | const,const | 1 | Using where; Using index |
- +----+-------------+-------+------+-----------------------+------------+---------+-------------+------+--------------------------+
- EXPLAIN SELECT * FROM `myTab` WHERE `nul` IS NOT NULL && notnul=2;
- +----+-------------+-------+-------+-----------------------+------------+---------+------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+-----------------------+------------+---------+------+------+--------------------------+
- | 1 | SIMPLE | myTab | range | notnul_nul,nul_notnul | notnul_nul | 10 | NULL | 1 | Using where; Using index |
- +----+-------------+-------+-------+-----------------------+------------+---------+------+------+--------------------------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement