Why using inner join itself is more faster than where

Question

Here is my table:

CREATE TABLE `cerp_oms_order` (
  `id` bigint NOT NULL,
  `company_id` bigint NOT NULL,
  `order_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `source_type` int NOT NULL,
  `shop_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `outer_shop` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `origin_status` int NOT NULL,
  `system_status` int NOT NULL,
  `created_time` datetime DEFAULT NULL,
  `paid_time` datetime DEFAULT NULL,
  `sent_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `delivery_deadline_time` datetime DEFAULT NULL,
  `amount` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `spu_kind` int NOT NULL,
  `sku_kind` int NOT NULL,
  `total_quantity` decimal(16,4) NOT NULL,
  `buyer_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `outer_buyer_identifier` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `tax_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `warehouse_owner` int DEFAULT NULL,
  `warehouse_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `logistics_type` int NOT NULL,
  `logistics_outer_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `delivery_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `delivery_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `wave_no` varchar(64) DEFAULT '',
  `is_deleted` tinyint NOT NULL DEFAULT '0',
  `backend_processing_type` tinyint NOT NULL,
  `create_type` tinyint NOT NULL,
  `is_hang_up` tinyint NOT NULL,
  `hang_up_case_type` smallint DEFAULT NULL,
  `hang_up_case_id` bigint DEFAULT NULL,
  `rc_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rm_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `vat` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `weight` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `volume` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `is_abnormal` tinyint NOT NULL DEFAULT '0',
  `estimate_profit` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `business_man_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `business_man` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `currency` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `net_amount_summary` decimal(19,4) NOT NULL,
  `domestic_amount` decimal(19,4) NOT NULL,
  `secret_key` varchar(64) DEFAULT '',
  `secretKey` varchar(255) DEFAULT NULL,
  `sale_id` bigint DEFAULT NULL,
  `total_refund_include_tax` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `total_refund_money` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `total_refund_tax` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `total_return_goods` decimal(16,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_order_no` (`order_no`),
  KEY `idx_order_company_id` (`company_id`,`created_time`),
  KEY `IDX_RM_TIME` (`rm_time`),
  KEY `IDX_IS_ABNORMAL` (`is_abnormal`),
  KEY `cerp_oms_order_company_id_index` (`company_id`),
  KEY `idx_order_company_status_deleted` (`company_id`,`is_deleted`,`system_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='system order table'

explain select *
from cerp_oms_order
         inner join (select id
                     from cerp_oms_order
                     where source_type = 43
                     order by created_time) as tmp using (id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	cerp_oms_order	null	ALL	PRIMARY	null	null	null	60787	10	Using where
1	SIMPLE	cerp_oms_order	null	eq_ref	PRIMARY	PRIMARY	8	cerp_oms_1.cerp_oms_order.id	1	100	null

using inner join execution: 7 ms, fetching: 109 ms

VS

explain
select *
from cerp_oms_order
where source_type = 43
order by created_time;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	cerp_oms_order	null	ALL	null	null	null	null	60787	10	Using where; Using filesort

using simple where clause execution: 80 ms, fetching: 138 ms

I don't understand why use inner join can speed up my sql?

Answer 1

If you had

INDEX(source_type, created_time)

both formulations would run faster. And, I think, the second would be faster.

As for "why". Look at what the second does:

1. Scan the entire table ("table scan") looking for rows with source_type = 43.
2. For each matching row, gather all the columns (*) from the row, into a temp table.
3. Sort that temp table. Note that this is bulkier than with the subquery that hauls around only id.
4. Deliver the results.

With the INDEX I suggest:

1. Look in the index's B+Tree for all 'rows' with source_type = 43.
2. Since those rows are sorted by created_time, not "sort" pass will be needed now or later.
3. Each of those index rows will contain id. (That's the way InnoDB gets to the rest of the columns), so
4. Look up the desired rows in the data's B+Tree and pull out all (*) the columns.
5. Deliver them.

Note that the JOIN version works essentially the same, but, technically speaking, the ORDER BY may get lost. If it does get lost, you would need to add a second ORDER BY, thereby forcing a sort.

(Unrelated) Your "KEY cerp_oms_order_company_id_index (company_id)," can be dropped since there are two other indexes starting with company_id.

More on optimizing indexes: Index Cookbook