SQL optimization: Association of two tables in millions, from 40 minutes to 3 seconds

SQL optimization: Join two tables of millions. The process from 40 minutes to 3 seconds [SQL] <strong> the table structure is as follows: </strong> [SQL] CREATE TABLE 'deviceback' ('id' int (11) NOT NULL AUTO_INCREMENT, 'imei' varchar (100) NOT NULL comment' unique identifier on mobile phone ', 'Mid 'varchar (50) default null, 'mac' varchar (100) default null, 'apntype' varchar (100) default null, 'status' int (11) DEFAULT '0', 'IP' varchar (100) default null, 'sn 'varchar (100) default null comment 'serial No.', 'oem 'varchar (1 00) default null comment 'vendor ', 'product' varchar (100) default null comment 'product', 'region' varchar (100) default null comment 'region ', 'operator' varchar (100) default null comment' operator ', 'sim' varchar (100) default null comment 'sim card No ', 'Push _ time' timestamp null default null comment' First Login time', 'origin _ version' varchar (100) default null comment 'original version', 'province 'varchar (100) default null comment 'province ', 'Primarycode' varchar (100) default null comment' province Code', 'city' varchar (50) default null comment 'city', 'citycode' varchar (50) default null comment 'city Code', 'Brands' varchar (50) DEFAULT '0', 'version' varchar (100) DEFAULT '0' COMMENT 'client version ', 'Last _ checktime' timestamp null default null comment' last logon time', primary key ('id'), fulltext key 'newindex1 '('imei '), fulltext key 'newindex2 '('mid '), Fulltext key 'newindex3 '('product'), fulltext key 'newindex4' ('Brands') ENGINE = MyISAM AUTO_INCREMENT = 6832460 default charset = utf8; [SQL] CREATE TABLE '2017 _ applog' ('id' int (11) NOT NULL AUTO_INCREMENT, 'imei 'varchar (20130602) DEFAULT NULL, 'mid' varchar (50) not null default '', 'status' char (1) not null default '0', 'mac' varchar (100) DEFAULT 'null', 'sn 'varchar (100) default null, 'sim 'var Char (100) default null, 'coperator' varchar (100) default null, 'version' varchar (100) default null, 'logintime' datetime default null, 'IP' varchar (20) default null, 'origin _ version' varchar (100) default null, 'Now _ version' varchar (100) default null, 'aptype' varchar (20) default null, 'oem 'varchar (20) default null, 'product' varchar (100) default null, 'region' varchar (100) default null, 'operato R 'varchar (100) default null, primary key ('id'), fulltext key 'newindex1 '('imei'), fulltext key 'newindex2' ('mid ')) ENGINE = MyISAM AUTO_INCREMENT = 3123866 default charset = utf8; SQL: [SQL] <strong> SELECT * FROM deviceback d, 20130602_AppLog g WHERE d. mid = g. mid AND d. imei = g. imei AND d. mac = g. mac AND d. brands = 0 AND g. coperator <> ''limit 20; </strong> explain result: the values of ALL and key are NULL, that is, full table scan without any index. Expired! 1. Then I suggest you add a composite index and try to see how it works: 2. The effect is still relatively slow. It seems that you have to try another method. Check all associated fields and change the null data ''. Still ineffective. 3. If you do not check *, count (*) to check whether the result set is large or small. The result is stuck and no query result is displayed in a short time. 4. Here I guess it is a problem of data composition. I will try to remove the conditions one by one. SELECT * FROM deviceback d, 20130602_AppLog gWHERE d. mid = g. mid AND d. imei = g. imei AND d. mac = g. mac AND d. brands = 0 limit 20; remove <> try the condition, God. The result is 37 seconds. Well, the problem is probably found. in <> This judgment statement, that is, AND g. coperator <>. Then, first create an index for the coperator field in the 20130602_AppLog table, and then check the number of g. coperator <> ''In 20130602_AppLog? The results are quite gratifying. The results are queried separately. There are more than records in less than one second. Select count (1) FROM 20130602_AppLog gg WHERE gg. coperator <> ''63987 then try the entire SQL statement to see how long it takes. First look at the explain result: SELECT * FROM deviceback d, 20130602_AppLog gWHERE d. mid = g. mid AND d. imei = g. imei AND d. mac = g. mac AND d. brands = 0 AND g. coperator <> ''limit 20; Great. The result is displayed in less than 3 seconds. 5. limit 20 is OK. If I want to try limit without limits, how long will it take to query the results. Directly execute SELECT * FROM deviceback d, 20130602_AppLog gWHERE d. mid = g. mid AND d. imei = g. imei AND d. mac = g. mac AND d. brands = 0 AND g. coperator <> ''; gets stuck. The result is not returned for a long time. Is it a data problem? Try to switch the order of fields following the where condition. SELECT d. mid, g. mac, FROM deviceback d, 20130602_AppLog gWHERE d. imei = g. imei AND d. mac = g. mac AND d. mid = g. mid AND d. brands = 0 AND g. coperator <> ''; OK, 27 seconds out, AND d. mid = g. the mid field is previously in the first one and is now placed behind it because the mobile phone uniquely identifies that this field has a null value. God, the most annoying null value of key business fields, brings great troubles to our optimization work. Remember: you must keep in mind that key business fields cannot be null.