An example of sql super slow recently is as follows

The table creation statement is as follows

CREATE TABLE `sys_dept` (
  `dept_id` int(10) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Department name',
  `sort` int(11) DEFAULT NULL COMMENT 'sort',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT 'Delete  -1: Deleted 0: OK',
  `parent_id` int(10) DEFAULT '0' COMMENT 'father id',
  `tenant_id` int(11) DEFAULT '1',
  PRIMARY KEY (`dept_id`) USING BTREE,
  KEY `index_dept_name` (`name`) USING BTREE,
  KEY `index_parent` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Department management';
CREATE TABLE `sys_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'User name',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Password',
  `mail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'mailbox',
  `cn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'cn',
  `distinguished_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Identifying names',
  `salt` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Random salt',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Full name',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Cell-phone number',
  `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Head portrait',
  `dept_id` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'department ID',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  `lock_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-Normal, 9-locking',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-Normal, 1-delete',
  `wx_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'WeChat openid',
  `qq_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'QQ openid',
  `tenant_id` int(11) DEFAULT '1' COMMENT 'Tenant',
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE KEY `user_idx1_username` (`username`) USING BTREE,
  KEY `user_wx_openid` (`wx_openid`) USING BTREE,
  KEY `user_qq_openid` (`qq_openid`) USING BTREE,
  KEY `12312` (`dept_id`) USING BTREE,
  KEY `21322` (`user_id`,`username`,`dept_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=37549810 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='User table';

In these two tables, we execute sql for 2000 pieces of data in Table 1 and 8000 pieces of data in Table 2 as follows:

SELECT
		`user`.user_id,
		`user`.username,
		`user`.name,
		`user`.`password`,
		`user`.salt,
		`user`.distinguished_name,
		`user`.cn,
		`user`.mail,
		`user`.phone,
		`user`.avatar,
		`user`.wx_openid,
		`user`.qq_openid,
		`user`.dept_id AS deptId,
		`user`.create_time AS ucreate_time,
		`user`.update_time AS uupdate_time,
		`user`.del_flag AS udel_flag,
		`user`.lock_flag AS lock_flag,
		`user`.tenant_id AS tenantId ,
		d.name
		FROM
		sys_user AS `user`
		left join sys_dept d  
		on `user`.dept_id=d.dept_id
		ORDER BY `user`.create_time DESC

The execution time is about 4 seconds, the speed is too slow, the experience is too poor

The sql statement is as follows

That is to say, the associated field is changed from the original varchar to int, which is not an order of magnitude, and the speed has reached 17 milliseconds.
summary

1. Table design: the primary key should be associated with fields of type int as much as possible to reduce database query conditions, because varchar will have a conversion process;
2. It is better to create index for related fields.
3. Tables with small amount of data should be in the front as much as possible