SELECT
T.s tr_code AS strCode,
T.s tr_unit AS strUnitName,
COUNT (m.u uid) AS intNum
The FROM
(SELECT str_code str_unit, ifnull (INT_ORDER, 0) AS intOrder FROM base_unit WHERE int_unit_level=1) t
LEFT the JOIN members m ON m.u nit_fenju_uid=t.s tr_code
AND Margaret spellings TR_MEMBER_TYPE='normal'
LEFT the JOIN GROUP_LINK l ON l.S TR_MEMBER_UID=m.u uid
WHERE
1=1
GROUP BY
T.s tr_code,
T.s tr_unit,
T.i ntOrder
The ORDER BY
COUNT (uid) m.u DESC,
T.i ntOrder ASC


Former to transplant found in mysql query good slow mysql to 120 s former seconds out

CodePudding user response:

1. GROUP_LINK table in the query useless;
2. In members. Try unit_fenju_uid upon index;

See the effect, and then continue,,,

CodePudding user response:

1. Left the join condition need to build indexes;
(2), t is not necessary to put inside the where condition in outer layer and finally, too;
3. Don't know your table structure, I don't know what is your group by use

CodePudding user response:

Where 1=1 this kind of condition, if found by our DBA, don't be batch to death just strange, SQL language to write this way, it is easy to cause a full table scan,

CodePudding user response:

Ifnullis null check in our DBA, can not appear, you must fill in value, while writing to my DB no field is allowed inside is empty, even date also has a false date value to fill in,

CodePudding user response:

Without your table structure, also don't understand your business, can only optimize this appearance, if the business permission, change left the join to join is quicker,
Another look at your several tables, indexes, how to add index, the baidu, a lot of content,
- SELECT
- t.s tr_code AS strcode,
- t.s tr_unit AS strunitname,
- COUNT (m.u uid) AS intnum
- the FROM
- base_unit t
M - LEFT the JOIN members ON
- m.u nit_fenju_uid=t.s tr_code
- AND
- Margaret spellings tr_member_type='normal'
- LEFT the JOIN group_link l ON l.s tr_member_uid=m.u uid
- WHERE
- t.i nt_unit_level=1
- GROUP BY
- t.s tr_code,
- t.s tr_unit,
- ifnull (t.i nt_order, 0)
- ORDER BY
- COUNT (uid) m.u DESC,
ASC - ifnull (t.i nt_order, 0);

CodePudding user response:

reference tonyxiao2019 reply: 3/f

where 1=1 this kind of condition, if found by our DBA, don't be batch to death just strange, SQL language to write this way, it is easy to cause a full table scan,

A few days ago in a book (a simple mysql) saw a condition for this kind of constant, mysql automatically optimize away (remove),