MySQL中如何优化IN子查询?
我的查询似乎与 http:// /dev.mysql.md/doc/refman/5.1/en/in-subquery-optimization.html,但不幸的是,本手册的收据对我不起作用。
当我单独运行它们时,内部查询和外部查询都使用索引,但是当我运行整个查询时,外部查询会扫描整个表...
mysql>解释 select smsId FROM SMSDelivery WHERE smsId IN (SELECT smsId FROM SMS WHERE phoneNumber='123456' OR fromUser='5678p' OR toUser='5124p') \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: SMSDelivery
type: index
possible_keys: NULL
key: FK75C784D70BE5EC9
key_len: 4
ref: NULL
rows: 1337017
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: SMS
type: unique_subquery
possible_keys: PRIMARY,phoneNumber,fromUser,toUser
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
My query seems to be very similar to example from http://dev.mysql.md/doc/refman/5.1/en/in-subquery-optimization.html, but unfortunately receipts from this manual don't work for me.
When I run them separately, both inner and outer queries use indexes, but when I run the whole query, outer query scans the whole table...
mysql> explain select smsId FROM SMSDelivery WHERE smsId IN (SELECT smsId FROM SMS WHERE phoneNumber='123456' OR fromUser='5678p' OR toUser='5124p') \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: SMSDelivery
type: index
possible_keys: NULL
key: FK75C784D70BE5EC9
key_len: 4
ref: NULL
rows: 1337017
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: SMS
type: unique_subquery
possible_keys: PRIMARY,phoneNumber,fromUser,toUser
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
你能试试这个吗:
can you try this: