Geek Answers Handbook

Why is this simple connection request significantly faster with a subquery?

I have two tables. order_detailswhich is 100,000 lines, and outboundwhich is 10,000 lines.

I need to join them in a column order_numberwhich is VARCHAR (50) for both. order_number is not unique in the 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 is my initial request, and it takes more than 60 seconds to start:

SELECT o.order_number
FROM outbound o
INNER JOIN order_details od
    ON o.order_number = od.order_number

This query gets the same results and takes less than a second to run:

SELECT o.order_number
FROM outbound o
INNER JOIN
(
    SELECT order_number
    FROM order_details
) od
ON (o.order_number = od.order_number)

This is surprising to me, because usually subqueries are much slower.

EXPLAIN ( , ) , derived2, , auto_key0. , , , , .

.

MySQL Ver 14.14. 5.6.35 Linux (x86_64) CentOS.

:

?

+6
1

MySQL . :

. , .

, . , .

, , order_number (CREATE INDEX...) . , .

+5

More articles:


All Articles