16. Simple SQL Optimization for mysql partitions 1. SQL Optimization for mysql partitions

16. Simple SQL Optimization for mysql partitions 1. SQL Optimization for mysql partitions

1. use show session status like '% Com _ %'; to view the execution frequency of each currently connected SQL statement: show global status like '% Com _ % '; you can view the SQL Execution frequency since the last mysql server was started up till now. 2. explain select * from tmp; mysql> explain select * from zi_emp where tid = 1000 \ G *************************** 1. row ************************* id: 1 select_type: SIMPLE table: zi_emp type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec) PS: in type, ALL is the most efficient, this means that the full table scan index type is full index scan range is index range scan ref is non-unique index or unique index prefix scan eq_ref unique index scan const/system single table only one match, for example, if the user table NULL does not access the table or index, for example, select 1 = 1; 3. haha, I found a good thing: explain extended select * from zi_emp; check what the mysql optimizer did before executing the SQL statement show warnings; mysql> show warnings \ G ***************************** 1. row *************************** Level: Note Code: 1003 Message: select 'bin '. 'zi _ emp '. 'tid' AS 'tid', 'bin '. 'zi _ emp '. 'tname' AS 'tname' from 'bin '. 'zi _ emp' 1 row in set (0.00 sec) PS: Yes, you see a complete SQL statement, that is to say, this may be the best SQL statement given by msyql. You can learn and reuse it. 4. explain partitions select * from zi_emp where xxxxx can be used to view the partition location of the current record

 

How can I optimize the mysql query speed? I have about 40 thousand records in the article table, and the SQL query is slow. How can I optimize it?

This SQL statement is very simple. It is unable to optimize a condition simply from the statement, but other methods can be used.
For example, is your guid a fixed-length string? If yes, the char type can be used. In addition, if the guid field is not a primary key and is frequently retrieved, you can create an index for this field, I believe that the query speed can be increased by at least 10 times

Optimize mysql SQL statements

Whether you want to optimize the SQL Performance or shorten the SQL length. This SQL statement is not complex. It is just a few nesting statements. You can run the execution plan to see where the performance is consumed and then optimize the SQL statement.

I guess the most performance-consuming part of your SQL is distinct. I don't think you need to do this here. Just remove it.