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

Source: Internet
Author: User
Tags mysql query

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.