|
Using temporary; Using filesort при наличии индекса
#38779052
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Исходная инфо:
Версия 5.6.19-1
Таблица ord:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
CREATE TABLE `ord` (
`ord_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ord_level` tinyint(1) unsigned NOT NULL DEFAULT '0',
`ord_cons` int(10) unsigned NOT NULL DEFAULT '0',
`ord_arch` int(10) unsigned NOT NULL DEFAULT '0',
`stage_id` int(10) unsigned NOT NULL DEFAULT '0',
`cust_id` mediumint(8) unsigned NOT NULL DEFAULT '1',
`cont_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`cont_id_tech` mediumint(8) unsigned NOT NULL DEFAULT '0',
`office_serv_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ord_start_time` int(10) unsigned NOT NULL DEFAULT '0',
`start_country_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`ord_start_addr` varchar(9) NOT NULL DEFAULT '',
`ord_stop_time` int(10) unsigned NOT NULL DEFAULT '0',
`stop_country_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`ord_stop_addr` varchar(9) NOT NULL DEFAULT '',
`stop2_country_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`ord_stop2_addr` varchar(9) NOT NULL DEFAULT '',
`ord_weight` float unsigned NOT NULL DEFAULT '0',
`ord_weight_kom` float unsigned NOT NULL DEFAULT '0',
`ord_volume` float unsigned NOT NULL DEFAULT '0',
`ord_qplace` smallint(5) unsigned NOT NULL DEFAULT '0',
`cons_rate` smallint(4) unsigned NOT NULL DEFAULT '0',
`transit_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`product_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`tr_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`cust_exp_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`dox_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`cont_id_dox` mediumint(8) unsigned NOT NULL DEFAULT '0',
`ord_date` int(10) unsigned NOT NULL DEFAULT '0',
`ord_payments` double NOT NULL DEFAULT '0',
`ord_trading_start` int(10) unsigned NOT NULL DEFAULT '0',
`ord_ldm` float unsigned NOT NULL DEFAULT '0',
`bc_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`loading_method_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`unloading_method_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`fc_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`service_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ord_recent_client` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`ord_id`),
KEY `ord_cons` (`ord_cons`) USING BTREE,
KEY `stage_id` (`stage_id`) USING BTREE,
KEY `cust_id` (`cust_id`) USING BTREE,
KEY `cont_id_tech` (`cont_id_tech`) USING BTREE,
KEY `ord_start_time` (`ord_start_time`) USING BTREE,
KEY `cont_id` (`cont_id`) USING BTREE,
KEY `product_id` (`product_id`) USING BTREE,
KEY `tr_type_id` (`tr_type_id`) USING BTREE,
KEY `ord_level` (`ord_level`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=86394 DEFAULT CHARSET=cp1251;
Таблица ord_ext:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
CREATE TABLE `ord_ext` (
`ord_id` int(10) unsigned NOT NULL,
`ord_name` varchar(90) NOT NULL DEFAULT '',
`country_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`ord_warn_ok` tinyint(1) unsigned NOT NULL DEFAULT '0',
`money_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`depot_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`ord_cust_req` varchar(60) NOT NULL DEFAULT '',
`ord_cmr` varchar(63) NOT NULL DEFAULT '',
`ord_cmr_date` date NOT NULL DEFAULT '0000-00-00',
`ord_cmr_office` tinyint(3) unsigned NOT NULL DEFAULT '0',
`quality_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`quality_comment` varchar(200) NOT NULL DEFAULT '',
`ord_start_addr2` varchar(750) NOT NULL DEFAULT '',
`ord_start_cont` varchar(200) NOT NULL DEFAULT '',
`ord_ex1` varchar(150) NOT NULL DEFAULT '',
`ord_stop_addr2` varchar(750) NOT NULL DEFAULT '',
`ord_stop2_addr2` varchar(750) NOT NULL DEFAULT '',
`ord_special` varchar(300) NOT NULL DEFAULT '',
`ord_loadnum` varchar(100) NOT NULL,
`ord_comment` varchar(1000) NOT NULL DEFAULT '',
`ord_apprice` varchar(100) NOT NULL,
`ord_sender` varchar(200) NOT NULL,
`ord_recipient` varchar(200) NOT NULL,
PRIMARY KEY (`ord_id`),
KEY `depot_id` (`depot_id`) USING BTREE,
KEY `ord_id` (`ord_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
при выполнении запроса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select SQL_CALC_FOUND_ROWS
stage.stage_id stage_id,
stage_type_name,
stage.ord_id ord_id,
ord_name,
cust.cust_name,
stage_must_date,
stage_have_date,
stage_comment,
stage_transp,
stage_driver_contacts
FROM stage
join stage_type using(stage_type_id)
left join cust on(stage.cust_id=cust.cust_id)
join ord on(stage.ord_id=ord.ord_id)
join ord_ext on(stage.ord_id=ord_ext.ord_id)
GROUP BY stage.stage_id;
1.
2.
3.
4.
5.
6.
7.
8.
9.
+----+-------------+------------+--------+-------------------------------------------------------------------------------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------------------------------------------------------------------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | ord_ext | ALL | PRIMARY,ord_id | NULL | NULL | NULL | 60733 | Using temporary; Using filesort |
| 1 | SIMPLE | stage | ref | PRIMARY,stage_type_id,ord_id,location,cust_id,stage_date,stage_inherited,stage_type | ord_id | 4 | trol.ord_ext.ord_id | 1 | NULL |
| 1 | SIMPLE | stage_type | eq_ref | PRIMARY | PRIMARY | 1 | trol.stage.stage_type_id | 1 | NULL |
| 1 | SIMPLE | cust | eq_ref | PRIMARY | PRIMARY | 4 | trol.stage.cust_id | 1 | Using where |
| 1 | SIMPLE | ord | eq_ref | PRIMARY | PRIMARY | 4 | trol.ord_ext.ord_id | 1 | Using index |
+----+-------------+------------+--------+-------------------------------------------------------------------------------------+---------+---------+--------------------------+-------+---------------------------------+
поиск по таблице ord_ext не использует индекс что приводит к медленному выполнению запроса, записи в таблицах ord и ord_ext имеют идентичные ID и отношение один к одному. В чем может быть причина ? |
|
|
