MySQL database A table connection query is slow, how to optimize?

Two tables connection queries are slow, but if you look at a table quickly, or join queries for other tables, it is fast.

Query statement (about half a second query time):

SELECT * FROM member m LEFT JOIN gift g ON g.mid = m.id WHERE m.status = 0 AND m.wid = 236 ORDER BY m.id DESC LIMIT 0,20

Member table structure (approx. 40,000 data):

CREATE TABLE ' member ' (' ID ' int (one) not null auto_increment, ' wid ' int (one) not null, ' WXID ' varchar () ' DEFAULT null, ' Wid_wxid ' varchar (+) default NULL, ' Cpai ' varchar (+) NOT null, ' name ' varchar () default null, ' sex ' varchar (TEN) D  Efault NULL, ' tel ' varchar (one) default null, ' Lxtel ' varchar (one) default null, ' Xingge ' varchar ' default null ', ' QQ ' int (one) default null, ' birthday ' varchar (#) default null, ' Cartype ' varchar () default NULL, ' Carclass ' varchar (20) Default NULL, ' bxtime ' varchar (default ' 0.00 '), ' next_bxtime ' varchar (Default ' 0.00 ', ' bytime ' varchar) defau LT ' 0.00 ', ' next_bytime ' varchar (default ' 0.00 '), ' bylong ' varchar (default ' 0.00 '), ' next_bylong ' varchar (DEF) Ault ' 0.00 ', ' engine ' varchar (() default null, ' Chejia ' varchar () default NULL, ' Danganhao ' varchar () default NULL  , ' buy_date ' varchar (default null, ' first_date ' varchar) default NULL, ' Weixiu_times ' tinyint (4) default NULL, ' Weixiu_money ' Decimal (8, 2) default null, ' Last_date ' varchar (() default null, ' Last_jieche ' varchar () default NULL, ' cpai_register ' varchar Default NULL, ' identify_number ' varchar (default null, ' order_number ' varchar) default NULL, ' Sale_date ' var char () default null, ' Carkilometre ' int (one) default ' 0 ', ' address ' varchar (+) default NULL, ' remark ' varchar (+) DEF Ault null, ' Is_check ' int (one) default ' 0 ', ' Password ' char (6) default null, ' Salt ' char (+) default NULL, ' Pay_passwor d ' char (+) default NULL, ' Pay_salt ' char (6) default null, ' status ' tinyint (4) Default ' 0 ', ' time ' varchar (NOT NULL) , ' headimgurl ' varchar ($) default null, ' nickname ' varchar (+) default NULL, ' Isattention ' tinyint (4) Default ' 0 ', ' Paid_price ' Decimal (8,2) Default ' 0.00 ', ' source ' int (one) default null, ' Add_date ' datetime default NULL, ' Add_by ' Varc Har (+) Default ', ' Update_date ' datetime default NULL, ' update_by ' varchar (+) Default ', PRIMARY KEY (' id ')) ENGINE =myisam auto_increment=59151 DEFAULT Charset=utf8; 

Gift table structure (approx. 4,000 data):

CREATE TABLE `member_giftamount` (  `id` int(16) NOT NULL AUTO_INCREMENT,  `wid` int(32) NOT NULL,  `mid` int(11) NOT NULL,  `auth_wid` int(11) DEFAULT NULL,  `wxid` varchar(64) DEFAULT NULL,  `tel` varchar(30) DEFAULT NULL,  `amount` decimal(8,2) DEFAULT '0.00',  `gift_amount` decimal(8,2) DEFAULT '0.00',  `refund_amount` decimal(8,2) DEFAULT '0.00',  `credits` int(11) DEFAULT '0',  `total` decimal(8,2) DEFAULT '0.00',  `market_total` decimal(8,2) DEFAULT '0.00',  `wid_wxid` varchar(64) DEFAULT NULL,  `wait_total` decimal(8,2) DEFAULT '0.00',  `add_date` datetime DEFAULT NULL,  `add_by` varchar(30) DEFAULT NULL,  `update_date` datetime DEFAULT NULL,  `update_by` varchar(30) DEFAULT NULL,  `sign_days` int(11) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=4254 DEFAULT CHARSET=utf8;

Reply content:

Two tables connection queries are slow, but if you look at a table quickly, or join queries for other tables, it is fast.

Query statement (about half a second query time):

SELECT * FROM member m LEFT JOIN gift g ON g.mid = m.id WHERE m.status = 0 AND m.wid = 236 ORDER BY m.id DESC LIMIT 0,20

Member table structure (approx. 40,000 data):

CREATE TABLE ' member ' (' ID ' int (one) not null auto_increment, ' wid ' int (one) not null, ' WXID ' varchar () ' DEFAULT null, ' Wid_wxid ' varchar (+) default NULL, ' Cpai ' varchar (+) NOT null, ' name ' varchar () default null, ' sex ' varchar (TEN) D  Efault NULL, ' tel ' varchar (one) default null, ' Lxtel ' varchar (one) default null, ' Xingge ' varchar ' default null ', ' QQ ' int (one) default null, ' birthday ' varchar (#) default null, ' Cartype ' varchar () default NULL, ' Carclass ' varchar (20) Default NULL, ' bxtime ' varchar (default ' 0.00 '), ' next_bxtime ' varchar (Default ' 0.00 ', ' bytime ' varchar) defau LT ' 0.00 ', ' next_bytime ' varchar (default ' 0.00 '), ' bylong ' varchar (default ' 0.00 '), ' next_bylong ' varchar (DEF) Ault ' 0.00 ', ' engine ' varchar (() default null, ' Chejia ' varchar () default NULL, ' Danganhao ' varchar () default NULL  , ' buy_date ' varchar (default null, ' first_date ' varchar) default NULL, ' Weixiu_times ' tinyint (4) default NULL, ' Weixiu_money ' Decimal (8, 2) default null, ' Last_date ' varchar (() default null, ' Last_jieche ' varchar () default NULL, ' cpai_register ' varchar Default NULL, ' identify_number ' varchar (default null, ' order_number ' varchar) default NULL, ' Sale_date ' var char () default null, ' Carkilometre ' int (one) default ' 0 ', ' address ' varchar (+) default NULL, ' remark ' varchar (+) DEF Ault null, ' Is_check ' int (one) default ' 0 ', ' Password ' char (6) default null, ' Salt ' char (+) default NULL, ' Pay_passwor d ' char (+) default NULL, ' Pay_salt ' char (6) default null, ' status ' tinyint (4) Default ' 0 ', ' time ' varchar (NOT NULL) , ' headimgurl ' varchar ($) default null, ' nickname ' varchar (+) default NULL, ' Isattention ' tinyint (4) Default ' 0 ', ' Paid_price ' Decimal (8,2) Default ' 0.00 ', ' source ' int (one) default null, ' Add_date ' datetime default NULL, ' Add_by ' Varc Har (+) Default ', ' Update_date ' datetime default NULL, ' update_by ' varchar (+) Default ', PRIMARY KEY (' id ')) ENGINE =myisam auto_increment=59151 DEFAULT Charset=utf8; 

Gift table structure (approx. 4,000 data):

CREATE TABLE `member_giftamount` (  `id` int(16) NOT NULL AUTO_INCREMENT,  `wid` int(32) NOT NULL,  `mid` int(11) NOT NULL,  `auth_wid` int(11) DEFAULT NULL,  `wxid` varchar(64) DEFAULT NULL,  `tel` varchar(30) DEFAULT NULL,  `amount` decimal(8,2) DEFAULT '0.00',  `gift_amount` decimal(8,2) DEFAULT '0.00',  `refund_amount` decimal(8,2) DEFAULT '0.00',  `credits` int(11) DEFAULT '0',  `total` decimal(8,2) DEFAULT '0.00',  `market_total` decimal(8,2) DEFAULT '0.00',  `wid_wxid` varchar(64) DEFAULT NULL,  `wait_total` decimal(8,2) DEFAULT '0.00',  `add_date` datetime DEFAULT NULL,  `add_by` varchar(30) DEFAULT NULL,  `update_date` datetime DEFAULT NULL,  `update_by` varchar(30) DEFAULT NULL,  `sign_days` int(11) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=4254 DEFAULT CHARSET=utf8;

Index Optimization: G table mid plus index; M table status and WID index; slow query to see if the index is used, try to make the rows count small dot

1. Build an index

   1. To index (ID,WID,WXID,WID_WXID) in the member table

   2. To index (ID,WID,MID,AUTH_WID,WXID) in the gift table

   3. If you often have fields in the Where condition, you also need to index them.

2. do not use the * number : List only required fields

3. SQL Reference : SQL I wrote it directly, not tested.

select 不要用* from (SELECT m.status,m.wid,m.id FROM member m WHERE m.status = 0 AND m.wid = 236) aLEFT JOIN gift g ON g.mid = a.id ORDER BY a.id DESC LIMIT 0,20

Create a foreign key for the Mid field of gift and associate the ID of the member table

There is no value in the reference, I do not understand the data!
http://www.zhihu.com/question/37777220

Trouble putting the execution plan on the stick.

Make an index, using a subquery.

