Mysql optimization Learning

Order|GROUP
BY |Distinct

Bytes ------------------------------------------------------------------------------------------------------------------------------

MySQL group

KEY 'accountid' ('ifpersonal', 'createduser', 'accountuser ')
Sql1:
SELECT createdUser FROM t_account WHERE ifPersonal = 1 AND createdUser> 90000081 group by ifPersonal, createdUser, accountUser
Extra: Using where; Using index for group-by (the so-called loose index)
Note:
1. The fields after group by are indexed and appear in the order of index (not all appear as 'compact 'indexes)
2. The condition in the where statement must be the field in the index. The order and whether it is a constant are irrelevant.
3. The field returned by select must be an index field.
4. If clustering functions are used, you can only operate on index fields.

Sql2:
SELECT createdUser FROM t_account WHERE ifPersonal = 1 AND createduser= 90000081 group by accountUser
SELECT 'accountadbalance 'FROM t_account WHERE ifPersonal = 1 group by createdUser, accountUser

Note:
Compact Indexes
1. Using indexes, indexes appear in sequence, starting from where to group
2. The where condition must be a constant.

Bytes ------------------------------------------------------------------------------------------------------------------------------

MysqlDISTINCT

Using index for distinct [optimal]
Using index for group-

SQL:
Select distinct ifpersonal from t_account
Select distinct ifpersonal from t_account where 'createduser'> 90000013

Same group by can be implemented through loose index scan or compact index Scan
Different from group by, distinct does not need to be sorted.
If you also use group by and group, and use aggregate functions like Max, you cannot avoid filesort.
Note the same as group

EXPLAIN SELECT DISTINCT group_id -> FROM group_message\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: rangepossible_keys: NULLkey: idx_gid_uid_gckey_len: 4ref: NULLrows: 10Extra: Using index for group-byEXPLAIN SELECT DISTINCT user_id -> FROM group_message -> WHERE group_id = 2\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: refpossible_keys: idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: constrows: 4Extra: Using WHERE; Using indexEXPLAIN SELECT DISTINCT user_id-> FROM group_message-> WHERE group_id > 1 AND group_id < 10\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: rangepossible_keys: idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: NULLrows: 32Extra: Using WHERE; Using index; Using temporaryEXPLAIN SELECT DISTINCT max(user_id)-> FROM group_message-> WHERE group_id > 1 AND group_id < 10-> GROUP BY group_id\G*************************** 1. row ***************************id: 1SELECT_type: SIMPLEtable: group_messagetype: rangepossible_keys: idx_gid_uid_gckey: idx_gid_uid_gckey_len: 4ref: NULLrows: 32Extra: Using WHERE; Using index; Using temporary; Using filesort

Bytes ------------------------------------------------------------------------------------------------------------------------------