Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
75
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.07 KB | None | 0 0
  1. ...
  2. WHERE
  3. `POP1` IS NOT NULL
  4. && `VT`='ABC'
  5. && (`SOURCE`='HOME')
  6. && (`alt` RLIKE '^[AaCcGgTt]$')
  7. && (`ref` RLIKE '^[AaCcGgTt]$')
  8. && (`AA` RLIKE '^[AaCcGgTt]$')
  9. && (`ref` = `AA` || `alt` = `AA`)
  10. LIMIT 10 ;
  11.  
  12. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  13. | 1 | SIMPLE | myTab | ref | vt_source_pop1_pop2,pop1_vt_source,... | vt_source_pop1_pop2 | 206 | const,const | 20040021 | Using where |
  14.  
  15. CREATE TEMPORARY TABLE `myTab` (`notnul` FLOAT, `nul` FLOAT);
  16. 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);
  17. SELECT * FROM `myTab`;
  18.  
  19. +--------+------+
  20. | notnul | nul |
  21. +--------+------+
  22. | 1 | NULL |
  23. | 1 | 2 |
  24. | 1 | NULL |
  25. | 1 | 2 |
  26. | 1 | NULL |
  27. | 1 | 2 |
  28. | 1 | NULL |
  29. | 1 | 2 |
  30. | 1 | NULL |
  31. | 1 | 2 |
  32. | 1 | NULL |
  33. | 1 | 2 |
  34. +--------+------+
  35.  
  36. CREATE INDEX `notnul_nul` ON `myTab` (`notnul`, `nul`);
  37. CREATE INDEX `nul_notnul` ON `myTab` (`nul`, `notnul`);
  38.  
  39. SHOW INDEX FROM `myTab`;
  40.  
  41. +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  42. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  43. +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  44. | myTab | 1 | notnul_nul | 1 | notnul | A | 12 | NULL | NULL | YES | BTREE | | |
  45. | myTab | 1 | notnul_nul | 2 | nul | A | 12 | NULL | NULL | YES | BTREE | | |
  46. | myTab | 1 | nul_notnul | 1 | nul | A | 12 | NULL | NULL | YES | BTREE | | |
  47. | myTab | 1 | nul_notnul | 2 | notnul | A | 12 | NULL | NULL | YES | BTREE | | |
  48. +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  49.  
  50. EXPLAIN SELECT * FROM `myTab` WHERE `notnul` IS NOT NULL;
  51. +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  52. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  53. +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  54. | 1 | SIMPLE | myTab | index | notnul_nul | notnul_nul | 10 | NULL | 12 | Using where; Using index |
  55. +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  56.  
  57.  
  58. EXPLAIN SELECT * FROM `myTab` WHERE `nul` IS NOT NULL;
  59. +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  60. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  61. +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  62. | 1 | SIMPLE | myTab | range | nul_notnul | nul_notnul | 5 | NULL | 6 | Using where; Using index |
  63. +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
  64.  
  65. EXPLAIN SELECT * FROM `myTab` WHERE `nul` IS NULL && notnul=2;
  66. +----+-------------+-------+------+-----------------------+------------+---------+-------------+------+--------------------------+
  67. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  68. +----+-------------+-------+------+-----------------------+------------+---------+-------------+------+--------------------------+
  69. | 1 | SIMPLE | myTab | ref | notnul_nul,nul_notnul | notnul_nul | 10 | const,const | 1 | Using where; Using index |
  70. +----+-------------+-------+------+-----------------------+------------+---------+-------------+------+--------------------------+
  71.  
  72.  
  73. EXPLAIN SELECT * FROM `myTab` WHERE `nul` IS NOT NULL && notnul=2;
  74. +----+-------------+-------+-------+-----------------------+------------+---------+------+------+--------------------------+
  75. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  76. +----+-------------+-------+-------+-----------------------+------------+---------+------+------+--------------------------+
  77. | 1 | SIMPLE | myTab | range | notnul_nul,nul_notnul | notnul_nul | 10 | NULL | 1 | Using where; Using index |
  78. +----+-------------+-------+-------+-----------------------+------------+---------+------+------+--------------------------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement