|
去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。
- SELECT *
- FROM my_neighbor n
- INNER JOIN message_info m
- ON n.id = m.neighbor_id
- AND m.inuser = 'xxx'
- LEFT JOIN my_neighbor_apply sra
- ON n.id = sra.neighbor_id
- AND sra.user_id = 'xxx'
- WHERE n.topic_status < 4
- AND n.topic_type <> 5
新的执行计划：
- +----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
- | 1 | SIMPLE | m | ref | | idx_message_info | 122 | const | 1 | Using index condition |
- | 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where |
- | 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |
- +----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
6、条件下推 （编辑：西安站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
