Home > Article > Backend Development > A certain table connection query in Mysql database is very slow, how to optimize it?

A certain table connection query in Mysql database is very slow, how to optimize it?

WBOYOriginal: 2016-08-04 09:21:181656browse

The connection query between two tables is very slow, but if you query one table alone, it is very fast, or the connection query of other tables is also very fast.

Query statement (about half a second query time):

<code>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</code>

member table structure (about 40,000 pieces of data):

<code>CREATE TABLE `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wid` int(11) NOT NULL,
  `wxid` varchar(30) DEFAULT NULL,
  `wid_wxid` varchar(30) DEFAULT NULL,
  `cpai` varchar(32) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `tel` varchar(11) DEFAULT NULL,
  `lxtel` varchar(11) DEFAULT NULL,
  `xingge` varchar(10) DEFAULT NULL,
  `qq` int(11) DEFAULT NULL,
  `birthday` varchar(12) DEFAULT NULL,
  `cartype` varchar(20) DEFAULT NULL,
  `carclass` varchar(20) DEFAULT NULL,
  `bxtime` varchar(20) DEFAULT '0.00',
  `next_bxtime` varchar(20) DEFAULT '0.00',
  `bytime` varchar(20) DEFAULT '0.00',
  `next_bytime` varchar(20) DEFAULT '0.00',
  `bylong` varchar(20) DEFAULT '0.00',
  `next_bylong` varchar(20) DEFAULT '0.00',
  `engine` varchar(50) DEFAULT NULL,
  `chejia` varchar(50) DEFAULT NULL,
  `danganhao` varchar(30) DEFAULT NULL,
  `buy_date` varchar(20) DEFAULT NULL,
  `first_date` varchar(20) DEFAULT NULL,
  `weixiu_times` tinyint(4) DEFAULT NULL,
  `weixiu_money` decimal(8,2) DEFAULT NULL,
  `last_date` varchar(20) DEFAULT NULL,
  `last_jieche` varchar(50) DEFAULT NULL,
  `cpai_register` varchar(20) DEFAULT NULL,
  `identify_number` varchar(20) DEFAULT NULL,
  `order_number` varchar(20) DEFAULT NULL,
  `sale_date` varchar(20) DEFAULT NULL,
  `carkilometre` int(11) DEFAULT '0',
  `address` varchar(32) DEFAULT NULL,
  `remark` varchar(32) DEFAULT NULL,
  `is_check` int(11) DEFAULT '0',
  `password` char(6) DEFAULT NULL,
  `salt` char(32) DEFAULT NULL,
  `pay_password` char(32) DEFAULT NULL,
  `pay_salt` char(6) DEFAULT NULL,
  `status` tinyint(4) DEFAULT '0',
  `time` varchar(24) NOT NULL,
  `headimgurl` varchar(200) DEFAULT NULL,
  `nickname` varchar(32) DEFAULT NULL,
  `isattention` tinyint(4) DEFAULT '0',
  `paid_price` decimal(8,2) DEFAULT '0.00',
  `source` int(11) DEFAULT NULL,
  `add_date` datetime DEFAULT NULL,
  `add_by` varchar(30) DEFAULT '',
  `update_date` datetime DEFAULT NULL,
  `update_by` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=59151 DEFAULT CHARSET=utf8;
</code>

gift table structure (about 4000 pieces of data):

<code>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;

</code>

Reply content:

The connection query between two tables is very slow, but if you query one table alone, it is very fast, or the connection query of other tables is also very fast.

Query statement (query time of about half a second):

<code>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</code>

member table structure (about 40,000 pieces of data):

<code>CREATE TABLE `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wid` int(11) NOT NULL,
  `wxid` varchar(30) DEFAULT NULL,
  `wid_wxid` varchar(30) DEFAULT NULL,
  `cpai` varchar(32) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `tel` varchar(11) DEFAULT NULL,
  `lxtel` varchar(11) DEFAULT NULL,
  `xingge` varchar(10) DEFAULT NULL,
  `qq` int(11) DEFAULT NULL,
  `birthday` varchar(12) DEFAULT NULL,
  `cartype` varchar(20) DEFAULT NULL,
  `carclass` varchar(20) DEFAULT NULL,
  `bxtime` varchar(20) DEFAULT '0.00',
  `next_bxtime` varchar(20) DEFAULT '0.00',
  `bytime` varchar(20) DEFAULT '0.00',
  `next_bytime` varchar(20) DEFAULT '0.00',
  `bylong` varchar(20) DEFAULT '0.00',
  `next_bylong` varchar(20) DEFAULT '0.00',
  `engine` varchar(50) DEFAULT NULL,
  `chejia` varchar(50) DEFAULT NULL,
  `danganhao` varchar(30) DEFAULT NULL,
  `buy_date` varchar(20) DEFAULT NULL,
  `first_date` varchar(20) DEFAULT NULL,
  `weixiu_times` tinyint(4) DEFAULT NULL,
  `weixiu_money` decimal(8,2) DEFAULT NULL,
  `last_date` varchar(20) DEFAULT NULL,
  `last_jieche` varchar(50) DEFAULT NULL,
  `cpai_register` varchar(20) DEFAULT NULL,
  `identify_number` varchar(20) DEFAULT NULL,
  `order_number` varchar(20) DEFAULT NULL,
  `sale_date` varchar(20) DEFAULT NULL,
  `carkilometre` int(11) DEFAULT '0',
  `address` varchar(32) DEFAULT NULL,
  `remark` varchar(32) DEFAULT NULL,
  `is_check` int(11) DEFAULT '0',
  `password` char(6) DEFAULT NULL,
  `salt` char(32) DEFAULT NULL,
  `pay_password` char(32) DEFAULT NULL,
  `pay_salt` char(6) DEFAULT NULL,
  `status` tinyint(4) DEFAULT '0',
  `time` varchar(24) NOT NULL,
  `headimgurl` varchar(200) DEFAULT NULL,
  `nickname` varchar(32) DEFAULT NULL,
  `isattention` tinyint(4) DEFAULT '0',
  `paid_price` decimal(8,2) DEFAULT '0.00',
  `source` int(11) DEFAULT NULL,
  `add_date` datetime DEFAULT NULL,
  `add_by` varchar(30) DEFAULT '',
  `update_date` datetime DEFAULT NULL,
  `update_by` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=59151 DEFAULT CHARSET=utf8;
</code>

gift table structure (about 4000 pieces of data):

<code>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;

</code>

Index optimization: add index to g table mid; add index to m table status and wid; slow query to see if the index is used, try to keep the number of rows as small as possible

Indexing
1. Create an index into (id,wid,wxid,wid_wxid) in the member table
2. Index (id,wid,mid,auth_wid,wxid) in the gift table
3. If some fields often appear in your where condition, you must also create an index.
Don’t use * sign: only list the required fields
SQL reference: The sql is written directly by me and has not been tested

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

Create a foreign key for the mid field of gift and associate it with the id of the member table

Is there any value for reference? I don’t know about data!
http://www.zhihu.com/question/37777220

Please post the execution plan

Create indexes using subqueries.

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：How to create WeChat Oauth web applicationNext article：How to create WeChat Oauth web application

See more

A certain table connection query in Mysql database is very slow, how to optimize it?

Reply content:

Related articles