Optimize a MySQL statement

Optimized a MySQL statement, no technical content, say the process:

The pre-optimization statements are as follows, with an approximate execution time of 1-2 seconds

Select U.id, U.s_user_name,u.s_user_img, (select count (a.n_copy_count) from t_fonts a joins T_practicerecords B on B.n_font _id=a.id where A.n_user_id=u.id and b.n_delete=0 Group by a.n_user_id) as N_copy_count,case when (select COUNT (id) from T_f  Riendships where n_user_id=11311 and n_friend_id=u.id) >0 then ' true ' else ' false ' end as is_collected from T_users u where BINARY ' s_user_name ' = ' do word little Helper ' order by LENGTH (S_USER_NAME) ASC, ID ASC Limit 0,10

The

Related execution plans are as follows:

Mysql> explain select  u.id ,u.s_user_name,u.s_user_img, (Select count (a.n_ Copy_count)  from t_fonts a  join t_practicerecords b on b.n_font_ ID=A.ID WHERE A.N_USER_ID=U.ID AND B.N_DELETE=0 GROUP BY A.N_USER_ID) as  n_copy_count,case when  (Select count (ID)  from t_friendships    where n_user_id=11311 and n_friend_id=u.id) >0 then  ' true '  else  ' false '  end as is_collected  from t_users u   where binary   ' s_user_name '  =  ' do Word Assistant '   order by length (s_user_name)  asc ,id  asc limit 0,10;+----+--------------------+---------------+------------+--------+-------------- -----------------------------------------------+---------------------------------+---------+------------------+ ---------+----------+-----------------------------+| id | select_type        |  table         | partitions | type    | possible_keys                                                 | key                               | key_len | ref               | rows    |  filtered | Extra                        |+----+--------------------+---------------+------------+--------+--------------- ----------------------------------------------+---------------------------------+---------+------------------+- --------+----------+-----------------------------+|  1 | primary             | u              | NULL       | ALL     | NULL                                                           | NULL                             | null     | null             | 1360606  |   100.00 | using where; using filesort | |   3 | DEPENDENT SUBQUERY | t_friendships | NULL        | eq_ref | primary,pk__t_friend__3213e83f571df1d5,user_index,n_user_id  | PRIMARY                          | 8        | const,sykdb.u.id |       1 |   100.00  | null                         | |   2 | DEPENDENT SUBQUERY | a              | NULL       | ref     | PRIMARY,n_user_index                                          | n_user_index                     | 4        | sykdb.u.id       |        7 |   100.00 | Using index  condition       | |   2 | dependent subquery | b             | null        | ref    | idx_fid_score_delete_uploadtime                               | idx_fid_score_delete_uploadtime | 5        | sykdb.a.id,const |       5 |    100.00 | Using index                  |+----+--------------------+---------------+------------+--------+-- -----------------------------------------------------------+---------------------------------+---------+------- -----------+---------+----------+-----------------------------+4 rows in set, 4  warnings  (0.00 seC 

Create an index on the S_user_name field of the T_users table and modify the table structure

Mysql> CREATE index Idx_user_name on t_users (s_user_name);  Query OK, 0 rows affected (5.55 sec) records:0 duplicates:0 warnings:0mysql> ALTER TABLE t_users modify S_user_name varchar (+) binary; Query OK, 1474778 rows Affected (45.23 sec) records:1474778 duplicates:0 warnings:0

Modify the statement to remove binary, again query as follows:

Mysql> select  u.id ,u.s_user_name,u.s_user_img, (Select count (A.n_copy_count)  from t_fonts a  join t_practicerecords b on b.n_font_id=a.id  WHERE A.N_USER_ID=U.ID AND B.N_DELETE=0 GROUP BY A.N_USER_ID) As n_ copy_count,case when  (Select count (ID)  from t_friendships   where  n_user_id=11311 and n_friend_id=u.id) >0 then  ' true '  else  ' false '   end as is_collected  from t_users u   where  ' S_user_name '  =  ' do Word Assistant '   order by length (s_user_name)  asc ,id asc limit  0,10;+----+-----------------+--------------------------------+--------------+--------------+| id  | s_user_name     | s_user_img                      | n_copy_count | is_collected |+----+-- ---------------+--------------------------------+--------------+--------------+|  2 |  Little assistant to do the word  | /0/1/photo/20160729035328.jpeg |          null | false        |+----+-----------------+--------------- -----------------+--------------+--------------+1 row in set  (0.00 sec)

However, it is prudent to modify the table structure.

This article is from the "just out of the shell of the Birds" blog, please be sure to keep this source http://qhd2004.blog.51cto.com/629417/1947038

Optimize a MySQL statement