mysql - Why is this simple join query significantly quicker with a sub-query? -
i have 2 tables. order_details
100,000 rows, , outbound
10,000 rows.
i need join them on column called order_number
, varchar(50) on both. order_number not unique in outbound table.
create table `outbound` ( `outbound_id` int(12) not null, `order_number` varchar(50) not null ) engine=innodb default charset=utf8; create table `order_details` ( `order_details_id` int(12) not null, `order_number` varchar(50) not null ) engine=innodb default charset=utf8;
this initial query, , takes on 60 seconds run:
select o.order_number outbound o inner join order_details od on o.order_number = od.order_number
this query gets same results , takes less second run:
select o.order_number outbound o inner join ( select order_number order_details ) od on (o.order_number = od.order_number)
this surprising me because sub-queries slower.
running explain
(which i'm still learning how understand) shows sub query version uses derived2
table, using index, , index auto_key0
. i'm not savvy enough know how interpret understand why makes significant difference.
i running these queries on command line.
i running mysql ver 14.14 distrib 5.6.35, linux (x86_64) centos.
in summary:
why simple join query quicker sub-query?
my knowledge of mysql limited. these thoughts:
your tables don't have indexes. join has read entire second table in order compare, each row of first table.
the subquery reads second table once , creates index, doesn't need read entire second table each row of first table. has check index, more faster.
to verify if i'm ritght or not, try creating indexes column order_number in 2 tables (create index ... ), , run again 2 queries. first query should take less second instead of minute.
Comments
Post a Comment