首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >强制mysql查询优化器在联接前使用WHERE条件

问强制mysql查询优化器在联接前使用WHERE条件
EN

Stack Overflow用户
提问于 2019-03-28 12:09:51
回答 1查看 39关注 0票数 0

我的查询连接了两个表,并应用了如下条件:

代码语言:javascript
复制
select b.address FROM  
address_partitions as c 
JOIN hop_addresses as b 
ON b.address=c.address 
AND c.npartition = 19;

+-----------------------------------+
| address                           |
+-----------------------------------+
| 111112UgMrsKETroaurM8YGf2R7SkN2wz |
+-----------------------------------+
1 row in set (10.08 sec)

mysql优化器总是先执行连接,然后执行条件:

代码语言:javascript
复制
| id   | select_type | table | type  | possible_keys      | key        | key_len | ref   | rows     | Extra                                                        |
|    1 | SIMPLE      | c     | ref   | PRIMARY,npartition | npartition | 4       | const |        1 | Using index                                                  |
|    1 | SIMPLE      | b     | index | NULL               | PRIMARY    | 38      | NULL  | 25249276 | Using where; Using index; Using join buffer (flat, BNL join) |

查询时间为10秒

如果我以两个步骤手动执行查询,首先使用条件:

  1. 条件优先

从address_partitions中选择地址为c,其中c.npartition = 19;

代码语言:javascript
复制
+-----------------------------------+
| address                           |
+-----------------------------------+
| 111112UgMrsKETroaurM8YGf2R7SkN2wz |
+-----------------------------------+
1 row in set (0.00 sec)
  1. 然后连接

从hop_addresses where address =“111112UGETroaurM8YGf2R7SkN2wz”中选择*;

代码语言:javascript
复制
+-----------------------------------+---------+-----------+--------------+
| address                           | no_hops | no_inputs | no_addresses |
+-----------------------------------+---------+-----------+--------------+
| 111112UgMrsKETroaurM8YGf2R7SkN2wz |       2 |        35 |            5 |
+-----------------------------------+---------+-----------+--------------+
1 row in set (0.00 sec)

查询时间为0.00秒

如何强制mysql查询优化器在联接之前执行WHERE条件?

代码语言:javascript
复制
address_partitions | CREATE TABLE `address_partitions` (
  `address` varchar(36) NOT NULL,
  `npartition` int(10) unsigned NOT NULL,
  PRIMARY KEY (`address`),
  KEY `npartition` (`npartition`)
) ENGINE=InnoDB

hop_addresses | CREATE TABLE `hop_addresses` (
  `address` varchar(36) NOT NULL,
  `no_hops` int(10) unsigned NOT NULL,
  `no_inputs` int(10) unsigned DEFAULT NULL,
  `no_addresses` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`address`)
) ENGINE=InnoDB
EN

回答 1

Stack Overflow用户

发布于 2019-03-28 13:35:08

您可以使用address_partitions表上的条件尝试子查询:

代码语言:javascript
复制
select b.address FROM (SELECT a.address FROM 
address_partitions as a 
WHERE a.npartition = 19) c
JOIN hop_addresses as b 
ON b.address=c.address;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55397310

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档