MySQL Query optimization tips

Source: Internet
Author: User
Tags mysql query mysql query optimization

Index optimization, query optimization, query caching, Server Setup optimization, operating system and hardware optimization, application-level optimization (Web server, caching) and more. The optimization techniques recorded here are more suitable for developers, are collected and collated from the web, mainly in query statement optimization, other aspects of the optimization techniques are not recorded here.

Cost metric for the query: execution time, number of rows checked, number of rows returned.

Several guidelines for indexing:

(1) Reasonable index can speed up the efficiency of data reading, and unreasonable indexing will slow down the response speed of the database.

(2) The more indexes, the slower the data is updated.

(3) try to use an index when using MyISAM as the engine (because MySQL stores the index btree) instead of InnoDB. But MyISAM does not support transcation.

(4) when your program and database structure/SQL statements have been optimized to the extent that they cannot be optimized, and the program bottleneck does not work, it is time to consider using a distributed cache system such as memcached.  

(5) habit and force yourself to use explain to analyze the performance of your SQL statements.

One, the optimization of the count

(1). Select COUNT (*) from world.city where ID > 5;

(2). Select (select count () from world.city) –count () from world.city where ID <= 5;  

1 Statement the number of rows that need to be scanned is more than 2 statements when the number of rows exceeds 11, and 2 statements scan 6 rows, in which case 2 statements are more efficient than 1 statements. Direct SELECT COUNT (*) from world.city is faster when there is no where statement, because MySQL always knows the number of rows in the table.


Second, the Index field on the operation will invalidate the index

try to avoid function or expression operations on the field in the Where clause, which will cause the engine to discard the use of the index full table scan. such as:

SELECT * from T1 where f1/2=100 should read: SELECT * from T1 where f1=100*2

Third , avoid using! = or ">, is null or is not NULL," in "," not "and so on operators

Because this makes the system unusable with indexes, it can only search the data in the table directly. For example: The Select ID from the employee WHERE ID! = "B%" Optimizer will not be able to determine the number of rows in the fatal row by index, so you need to search all rows of the table. In the in statement can use the EXISTS statement instead of the exists.

Four , rational use of exists,not exists clauses

Five , if you can use between, do not use in

Six , you can use distinct without GROUP by

Seven , try not to use the SELECT INTO statement. The SELECT into statement causes the table to lock and prevent other users from accessing the table

Viii. If multiple data is inserted into the same table at one time in the program

such as the following statement:  

Insert into person (name,age) VALUES (' Xboy ', 14);

Insert into person (name,age) VALUES (' Xgirl ', 15);

Insert into person (name,age) VALUES (' Nia ', 19);

It will be more efficient to put it into a statement.

Insert into person (name,age) VALUES (' Xboy ', +), (' Xgirl ', +), (' Nia ', 19);

MySQL Query optimization tips

Related Article

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.