On the first two tables in the DDL statements (for MySQL database) :

The CREATE TABLE ` goods_order ` (
` GOODS_ORDER_ID ` int (12) NOT NULL AUTO_INCREMENT COMMENT 'material order ID',
` GOODS_ORDER_NO ` varchar (50) DEFAULT NULL COMMENT 'order serial number order type organization + + system ID + sequence',
` SOU_ORGAN_ID ` int (12) DEFAULT NULL COMMENT 'order source organization,
DEFAULT NULL ` SOU_SYS ` int (2) the COMMENT 'order source system,
` OPR_USER_ID ` int (12) DEFAULT NULL COMMENT 'order under a single,
DEFAULT NULL ` SERVICE ` int (2) the COMMENT 'order type,
` CRT_TIME ` datetime DEFAULT NULL COMMENT 'order time,
` STATUS ` varchar (4) the DEFAULT NULL COMMENT 'order STATUS',
` GOODS_ORDER_DESC ` varchar (500) the DEFAULT NULL COMMENT 'order remarks'
` REL_ORDER_ID ` varchar (50) DEFAULT NULL COMMENT 'association external order ID',
` STORE_IDS ` varchar (100) the DEFAULT NULL COMMENT 'involves the warehouse ID,
` IN_STORE_ID ` int (12) DEFAULT NULL COMMENT 'storage warehouse ID,
` OUT_STORE_ID ` int (12) DEFAULT NULL COMMENT 'outbound warehouse ID,
` order_id ` bigint (20) DEFAULT NULL COMMENT 'involves the order 1,
` flow_inst_id ` int (11) the DEFAULT NULL,
PRIMARY KEY (` GOODS_ORDER_ID `)
) ENGINE=InnoDB AUTO_INCREMENT=3955 DEFAULT CHARSET=utf8;


The CREATE TABLE ` goods_order_item ` (
` GOODS_ORDER_ITEM_ID ` int (12) NOT NULL AUTO_INCREMENT COMMENT 'order detail ID',
` GOODS_ORDER_ID ` int (12) DEFAULT NULL COMMENT 'order ID',
DEFAULT NULL ` SERVICE ` int (2) the COMMENT 'order type,
` EQUIPMENT_INST_ID ` int (12) DEFAULT NULL COMMENT 'material instance ID,
` PRE_COUNT ` float (12, 2) the DEFAULT NULL,
` REL_COUNT ` float (12, 2) the DEFAULT NULL,
` PRICE ` float (12, 2) the DEFAULT NULL COMMENT 'material unit PRICE',
` SUPPLIER_ID ` int (12) DEFAULT NULL COMMENT 'material suppliers'
` TAR_POSITION_ID ` int (12) DEFAULT NULL COMMENT 'material home location',
` TAR_SG_REL_ID ` int (12) DEFAULT NULL COMMENT 'material belonging to detail,
` SOU_SG_REL_ID ` int (12) DEFAULT NULL COMMENT 'source material detail,
` USE_USER_ID ` int (12) DEFAULT NULL COMMENT 'material drawing people',
` RET_USER_ID ` int (12) DEFAULT NULL COMMENT 'material returned people',
` EQUIPMENT_AREA_ID ` int (12) DEFAULT NULL COMMENT 'equipment to enable location,
` EQUIPMENT_POSITION ` varchar (100) the DEFAULT NULL COMMENT 'equipment enable location details,
` EQUIPMENT_AREA_POINT_ID ` int (12) DEFAULT NULL COMMENT 'position enable hotspot,
` HOUSE_ID ` int (12) DEFAULT NULL COMMENT 'enable room number (for water and electricity gas meter devices)',
` PRE_COUNT2 ` int (12) DEFAULT NULL COMMENT 'new equipment estimated quantity (equipment) using alternate',
` REL_COUNT2 ` int (12) DEFAULT NULL COMMENT 'actual number new equipment (equipment) using alternate',
` TAR_OPR ` int (12) DEFAULT NULL COMMENT 'equipment clean place,
` CRT_TIME ` datetime DEFAULT NULL COMMENT 'order time,
` GO_ITEM_DESC ` varchar (50) DEFAULT NULL COMMENT 'remarks',
` initial_price ` varchar (10) DEFAULT NULL COMMENT 'selling price',
` deliver_price ` varchar (10) DEFAULT NULL COMMENT 'standard price,
` users ` varchar (300) the DEFAULT NULL,
` SELL_PRICE ` varchar (10) the DEFAULT '0' COMMENT 'selling price',
PRIMARY KEY (` GOODS_ORDER_ITEM_ID `),
The KEY ` goods_order_item_index ` (` SOU_SG_REL_ID `),
) ENGINE=InnoDB AUTO_INCREMENT=7108 DEFAULT CHARSET=utf8;


1, first look at the current SQL
SELECT * FROM GOODS_ORDER H, GOODS_ORDER_ITEM G
WHERE G.G OODS_ORDER_ID=h.g. OODS_ORDER_ID;
Execute the following plan

The execution time of 0.2 S
2, the alter TABLE goods_order_item add INDEX goods_order_id_index (goods_order_id)
An index, the execution plan to the following:

The execution time of 0.4 S

I coming out of the question, why added index but also significantly fewer rows, the number should be less ah, why the execution time is increased a lot, a great god help solve

CodePudding user response:

Because the main index on the useless, child table can use index,

CodePudding user response:

In addition, if this query is a select *, the role of the index is not so big, the only role is associated with convenient, it is better to a full table scan,

Index only in a few data query, can do wonders

CodePudding user response:

reference 1st floor yupeigu response:

didn't work because the main index, child table can use index,

Point is wrong, that point to be useful, I lost but point a plank brick, but a little doubt that child table using index query on the slow

CodePudding user response:

reference qq_34276072 reply: 3/f

Quote: refer to 1st floor yupeigu response:

Because the main index on the useless, child table can use index,

Point is wrong, that point to be useful, I lost but point a plank brick, but a little doubt that a indexing query should slow child table used

This is not certain,

In addition to the index, and an influence factor, is given priority to with the link, this may also affect performance, should give priority to in order to record number less commonly, to associate the record number of large,

Your table of how many, how many child table, how many connection?