Using mysql, recently met with two tables associated query, checked some data didn't also can solve, fans users than table 4 w, fan label table of more than 2000 data, a query down to 19 seconds, be don't bird,,, and master the way,
Wx_fans table, openid is a primary key, subscribetime keyword index is the full text, the other for the common information fields;
Wx_fans_tag_r table, openid is full-text keyword indexing, this table is only two fields, openid and tagId (is also more than a pair, one fan has multiple tags)
Below is the query:

The SELECT t.o penid subscribetime, createtime, subscribeStatus, nickname, nicknameStr, gender, country, province, city, headimgurl, STATUS, remark, groupId, r.t agId FROM wx_fans t
ON the LEFT OUTER JOIN wx_fans_tag_r r t.o penid=r.o penid WHERE 1=1
The ORDER BY subscribetime DESC
LIMIT 0, 20

The following is the result of the EXPLAIN SQL:


Here is SHOW the INDEX FROM wx_fans results:


Here is SHOW the INDEX FROM wx_fans_tag_r results:



Seek advice!!!!!!!!!!

CodePudding user response:

Create two index
The alter table wx_fans add key (subscribetime, the opened);
The alter table wx_fans_tag_r add key (the opened)

CodePudding user response:

reference 1st floor ZJCXC response:

set up two index
The alter table wx_fans add key (subscribetime, the opened);
The alter table wx_fans_tag_r add key (the opened)

The second index is don't need a screenshot shows btree index

CodePudding user response:

reference 1st floor ZJCXC response:

set up two index
The alter table wx_fans add key (subscribetime, the opened);
The alter table wx_fans_tag_r add key (the opened)

Have created, is still very slow slow,,

CodePudding user response:

The first index where there is?
You this is not a full text search, built a full-text index?

CodePudding user response:

reference 4 floor ZJCXC response:

1 index where there is?
You this is not a full text search, built a full-text index?

Then add a

CodePudding user response:

Join_buffer_size up try

CodePudding user response:

What is added after the execution of the plan?
Explain the select...