Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Here is my query:
- SELECT STRAIGHT_JOIN p.id FROM pool p LEFT JOIN sent s ON s.campid = 'YA1LGfh9' AND p.username = s.username WHERE p.gender = 'f' AND s.username IS NULL;
- Here are my indexes:
- mysql> show index from main.pool;
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | pool | 0 | PRIMARY | 1 | id | A | 9326886 | NULL | NULL | | BTREE | |
- | pool | 1 | username | 1 | username | A | 9326886 | NULL | NULL | | BTREE | |
- | pool | 1 | source | 1 | source | A | 2 | NULL | NULL | | BTREE | |
- | pool | 1 | location | 1 | location | A | 38382 | NULL | NULL | | BTREE | |
- | pool | 1 | pdex | 1 | gender | A | 3 | NULL | NULL | | BTREE | |
- | pool | 1 | pdex | 2 | username | A | 9326886 | NULL | NULL | | BTREE | |
- | pool | 1 | pdex | 3 | id | A | 9326886 | NULL | NULL | | BTREE | |
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- mysql> show index from main.sent;
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | sent | 0 | PRIMARY | 1 | primary_key | A | 351 | NULL | NULL | | BTREE | |
- | sent | 1 | username | 1 | username | A | 175 | NULL | NULL | | BTREE | |
- | sent | 1 | sdex | 1 | campid | A | 7 | NULL | NULL | | BTREE | |
- | sent | 1 | sdex | 2 | username | A | 351 | NULL | NULL | | BTREE | |
- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- Here is the EXPLAIN:
- mysql> explain SELECT STRAIGHT_JOIN p.id FROM pool p LEFT JOIN sent s ON s.campid = 'YA1LGfh9' AND p.username = s.username WHERE p.gender = 'f' AND s.username IS NULL;
- +----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------------+
- | 1 | SIMPLE | p | index | pdex | pdex | 161 | NULL | 9326886 | Using where; Using index |
- | 1 | SIMPLE | s | index | sdex | sdex | 309 | NULL | 351 | Using where; Using index; Not exists |
- +----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------------+
- 2 rows in set (0.00 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement