The following articles mainly describe five useful methods for MySQL query optimization. Anyone familiar with SQL statements knows that if you want to operate a task, SQL statements can be written in many ways, but the query performance varies with different writing methods.
This article lists five MySQL query
Tags: mysql indexing principle and slow query optimizationFirst, Introduction1, why should have index?General application system, reading and writing ratio of about 10:1, and the insertion operation and the general update operation rarely appear performance problems, in the production environment, we encounter the most, is also the most prone to problems, or some complex query operations, so the
Label:First, why the query slowNetwork, CPU calculation, generated statistics, execution technology, lock wait, io wait, etc.Second, slow query optimization access to inefficient query processing methods:
Confirm that you have accessed too many rows, or too many columns
Have you analyzed a large amount of
1. IntroductionIn the early stage of application system development, because the database data is relatively small, for querying SQL statements, the use of indexes and the writing of complex views can not understand the performance of the SQL statement of various ways, but the application system in practice, with the data in the database increases, The response speed of the system is one of the most important problems that the system needs to solve at present. An important aspect of system
The MySQL Query optimization program [group chart] analyzes a selection row query to see if it can be optimized to make it run faster. This article will study how the query optimization program works. For more information, see "Getting Maximum Performance from MySQL" in MySQ
MySQL query optimization: profile function
MySQL query optimization: profile function. Note that you need to install the profile module.
1. The version is later than 5.0.37. (Show profiles and show profile were added in MySQL 5.0.37 .)
SELECT @ profiling;To check whether the profile has been enabled. If the profilng va
We all know that in MySQL database query optimization, the actual application of the database {for example, MySQL (the best combination with PHP)} means the actual operation and use of related tools. Using indexes and using EXPLAIN to analyze queries and adjust the internal configuration of MySQL (the best combination with PHP) can optimize queries. Any database program
We all know that in MySQL database
storing the time with a string, and storing the IP address with an integer data type. (3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL. Columns that contain null values are very difficult to query optimization. Because they make indexes, index statistics, and comparison operations more complex. You should replace the null value with 0, a special valu
This paper summarizes and analyzes the techniques of MySQL query optimization. Share to everyone for your reference, specific as follows:
People who are familiar with SQL statements know that if you want to operate on a task, the SQL statement can have a lot of relevant writing, but different ways of writing query performance may be a great difference.
This art
copy of the original table, but in fact each server has only one member table and a distributed partition view. The data location is transparent to the application.11. Rebuild the index DBCC reindex, DBCC indexdefrag, shrink data and log DBCC shrinkdb, and DBCC shrinkfile.Set automatic log shrinking. Do not set Automatic database growth for large databases, which will reduce server performance.
T-SQL is very well written. The following lists common points:First, the DBMS processes the
disadvantages of this approach are almost identical to "test three" , except that the first generation of execution plans is not affected by the parameters.The following two stored procedures, after the creation of a stored procedure, no matter who executes first, the query plan is the same!EXEC P_test2 @ProductID = 870EXEC P_test2 @ProductID = 897This is the real use of the so-called "parameter sniffing"! When the
Anyone familiar with SQL in MySQL's five query optimization methods knows that SQL may write a variety of statements to complete the same task, but the query performance of different statements may be quite different, this article lists five query optimization methods. of co
In general, Oracle databases operate more efficiently than SQL Server databases in large data environments. From the query optimization alone, the two are quite different. Below, the author of the database query optimization of the difference to do a description, if there is not accurate place, but also please criticiz
Query optimization on SQL statements.
The foreground application works on the database server, and ultimately relies on the SQL statements in the application. According to incomplete statistics, SQL statements consume about 80% of the database server's resources. Therefore, how to improve the execution efficiency of SQL statements is a problem that must be considered in database
, MySQL will not log slow query How to turn on slow query: 1. Close the current MySQL service: net stop MySQL 2, start through Safe mode will write log. 3. Turn off MySQL with Safe mode activated 4, set the time of the slow query to 0.5 seconds, set long_query_time=0.5 5. Select * FROM table where xx= ' 00 '; 6, go to the slow
This article describes how to optimize MySQL database queries in three ways. We all know that during the actual MySQL query optimization process, database applications, such as MySQL (the best combination with PHP), mean the operation and use of the tool.
Using indexes, using EXPLAIN to analyze queries, and adjusting the internal configuration of MySQL (the best combination with PHP) can optimize queries.
A
Label:Good text, in case of loss, so turn it, the other layout to do a simple optimization. Original address: http://ourmysql.com/archives/1401 index PurposeThe goal of the index is to improve the efficiency of the query, you can analogy dictionary, if you want to check the word "MySQL", we definitely need to locate the letter M, and then find the Y letter from the bottom, and then find the remaining SQL. I
.
Although using not exists does not save the efficiency of the last stored procedure, using top keywords in SQL Server is a wise choice. Because the ultimate goal of paging optimization is to avoid generating too many record sets, we have mentioned the top advantage in the previous sections. Using Top, we can control the data volume.
In PagingAlgorithmThere are two key factors that affect the query spee
MySQL Query statement execution process and performance optimization-basic concepts and explanations
BitsCN.com
MySQL Query statement execution process and performance optimization-basic concepts and explanations
The key aspect of website or service performance is the database design (assuming you have chosen the app
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.