Advertisement
Guest User

Untitled

a guest
Jun 11th, 2012
200
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.83 KB | None | 0 0
  1. Here is my query:
  2.  
  3. 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;
  4.  
  5. Here are my indexes:
  6.  
  7. mysql> show index from main.pool;
  8. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  9. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  10. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  11. | pool | 0 | PRIMARY | 1 | id | A | 9326886 | NULL | NULL | | BTREE | |
  12. | pool | 1 | username | 1 | username | A | 9326886 | NULL | NULL | | BTREE | |
  13. | pool | 1 | source | 1 | source | A | 2 | NULL | NULL | | BTREE | |
  14. | pool | 1 | location | 1 | location | A | 38382 | NULL | NULL | | BTREE | |
  15. | pool | 1 | pdex | 1 | gender | A | 3 | NULL | NULL | | BTREE | |
  16. | pool | 1 | pdex | 2 | username | A | 9326886 | NULL | NULL | | BTREE | |
  17. | pool | 1 | pdex | 3 | id | A | 9326886 | NULL | NULL | | BTREE | |
  18. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  19.  
  20. mysql> show index from main.sent;
  21. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  22. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  23. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  24. | sent | 0 | PRIMARY | 1 | primary_key | A | 351 | NULL | NULL | | BTREE | |
  25. | sent | 1 | username | 1 | username | A | 175 | NULL | NULL | | BTREE | |
  26. | sent | 1 | sdex | 1 | campid | A | 7 | NULL | NULL | | BTREE | |
  27. | sent | 1 | sdex | 2 | username | A | 351 | NULL | NULL | | BTREE | |
  28. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  29.  
  30.  
  31. Here is the EXPLAIN:
  32.  
  33. 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;
  34. +----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------------+
  35. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  36. +----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------------+
  37. | 1 | SIMPLE | p | index | pdex | pdex | 161 | NULL | 9326886 | Using where; Using index |
  38. | 1 | SIMPLE | s | index | sdex | sdex | 309 | NULL | 351 | Using where; Using index; Not exists |
  39. +----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------------------+
  40. 2 rows in set (0.00 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement