The SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for data_detail
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` data_detail `;
The CREATE TABLE ` data_detail ` (
UNSIGNED ` id ` bigint (20) NOT NULL,
` content ` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'text',
` create_by ` int (11) NULL DEFAULT NULL COMMENT 'founder,
` data_set_id ` int (11), NOT NULL COMMENT 'data set number,
` size ` double (255, 0) NULL DEFAULT NULL COMMENT 'file size,
` status ` tinyint (3) the UNSIGNED NULL DEFAULT NULL COMMENT '0: not marked 2: artificial mark 1: automatic tagging',
` source ` tinyint (3) the UNSIGNED NULL DEFAULT 2 COMMENT 'data source: 1: upload; 2: manually create; 3: correct operation management; '
` mkey ` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT ',
` remark ` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'remarks, business system with'
` origin_id ` bigint (20) UNSIGNED NOT NULL COMMENT 'the one based on the initial data change come over',
` create_time ` datetime (0) NULL DEFAULT NULL COMMENT 'creation time,
` update_time ` datetime (0) NULL DEFAULT NULL COMMENT 'update time,
` version_id ` int (11) NULL DEFAULT NULL,
` update_by ` int (11) NULL DEFAULT NULL COMMENT 'update people',
PRIMARY KEY (` id `, ` data_set_id `) USING BTREE,
The INDEX ` status_data_set_id ` (` status `, ` data_set_id `) USING BTREE,
The INDEX ` id ` (` id `) USING BTREE,
The INDEX ` create_time ` (` create_time `) USING BTREE,
The INDEX ` indexid ` (` create_time `, ` id `) USING BTREE
) ENGINE=MyISAM CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='details data content ROW_FORMAT=Dynamic;
The SET FOREIGN_KEY_CHECKS=1;
Table 2 data_detail_delete
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for data_detail_delete
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` data_detail_delete `;
The CREATE TABLE ` data_detail_delete ` (
` data_detail_id ` bigint (20) UNSIGNED NOT NULL COMMENT 'data set details id',
` origin_id ` bigint (20) UNSIGNED NULL DEFAULT NULL COMMENT 'original id \ r \ n the most primitive data details id',
` version_id ` int (11) NULL DEFAULT NULL COMMENT 'version id',
` data_set_id ` int (11), NOT NULL COMMENT 'id data sets,
PRIMARY KEY (` data_detail_id `, ` data_set_id `) USING BTREE,
The INDEX ` version_data_set_id_index ` (` version_id `, ` data_set_id `) USING BTREE COMMENT 'INDEX'
) ENGINE=MyISAM CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;
The SET FOREIGN_KEY_CHECKS=1;
Table 3 data_tag_reference
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for data_tag_reference
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` data_tag_reference `;
The CREATE TABLE ` data_tag_reference ` (
` id ` int (11), NOT NULL AUTO_INCREMENT COMMENT 'primary key id,
` detail_id ` bigint (20) UNSIGNED NULL DEFAULT NULL COMMENT 'data details id',
` type ` tinyint (3) the UNSIGNED NULL DEFAULT NULL COMMENT 'table types: 1, type 1, 2, and type 2, 3, type 3',
` data_set_id ` int (11) NULL DEFAULT NULL COMMENT 'id data sets,
` tag_id ` int (11) NULL DEFAULT NULL COMMENT 'id tag,
` tag_set_id ` int (11) NULL DEFAULT NULL COMMENT 'tag library id',
PRIMARY KEY (` id `) USING BTREE,
The INDEX ` detail_id ` (` detail_id `) USING BTREE,
The INDEX ` isUesd ` (` type `, ` tag_id `, ` tag_set_id `) USING BTREE,
The INDEX ` detail_id_2 ` (` detail_id `, ` data_set_id `) USING BTREE,
The INDEX ` tagid ` (` tag_id `, ` detail_id `, ` data_set_id `) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=58544606 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;
The SET FOREIGN_KEY_CHECKS=1;
Need the optimized SQL
SELECT
DISTINCT (dd id), dd. *
The FROM
Data_detail dd
Straight_join data_tag_reference DTR ON dd. Id=DTR. Detail_id
AND the dd. Data_set_id=DTR. Data_set_id
WHERE
1024 & gt;=dd. Version_id
AND chemical data_set_id=253
AND dd id NOT IN (SELECT data_detail_id FROM data_detail_delete WHERE 1002 & gt;=version_id AND data_set_id=253)
AND DTR. Tag_id in (94110690)
AND ` content ` like concat (' % ', 'a', '%')
The ORDER BY
Dd. Create_time Desc
LIMIT 0,
50
Three tables is large amount of data,
Group by id and order by create_time DESC conflict of the two field, bad solution, and at the same time using the order by and group by the two fields, using filesort file sorting, full table sorting, lead to at least 4 to 5 seconds, if no order by create_time, only 0.03 s, test in mysql8.0 don't have the conflict, will use Backward index scan descending index, will be very fast, the system USES the mysql5.7 can only find other optimization methods, data set details and must be in accordance with the creation time descending sort this requirement, this order by SQL cannot remove, spent a lot of time here, and all sorts of index are tried,
Group by id is to heavy,
In mysql8, can use the distinct (id), and use the Backward index scan, mysql 8 of this new feature, speed is very fast, but the company can't use mysql8 again, MariaDb10.5 tried, the same statement, index, mysql8 run in 0.03 s, mariaDb used 7 s, I think explain this Backward difference is within the index scan, besides mysql8, other database is used by filesort,
Everyone a great god, and see if there is any way to optimize this sentence,
Mainly is the index to add,
Where