steps to optimize sql query

In the course of our development, in particular, the development of management systems, often encounter multiple conditions query (or called indeterminate conditions) of the case, is provided to the user input query conditions have several different query fields, and, in the actual use of the user will not be able to determine what conditions to use as a search c

each plan depends on the statistical results collected by the system, but these data are not necessarily the latest. Because you may know more about the database and data content than query optimizer, you can decide a better plan than query optimizer. Using the information provided by query analyzer, you can determine whether the plan provided by

waiting lock time, the total number of result rows, and the total number of rows scanned. Count, the number of executions of SQL and the percentage of total slow log count. Time, execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time. 95% of time, removing the fastest and slowest SQL, coverage accounted

data into the buffer zone, no locks are required, when the sub-thread is dumping data, you only need to lock and then adjust the pointers of the two buffers (I suspect this lock can also be avoided ). they perform their respective duties without harassment, which is simple and efficient. 4) Step 3 optimization: evenly distribute the data imported to the database as much as possibleThis step is roughly the same as the previous step. It only sets a volume when the sub-thread is dumping data. When

Method 1 to optimize the PHP program. Ifamethodc ++ anbestatic, declareitstatic. Speedimprovementisbyafactorof4. if a method can be static, it should be declared as static. The speed can be increased to 4 times. 2. echoisfasterthanprint. echo method for optimizing PHP programs than print 1. If a method c ++ an be static, declare it static. Speed improvement is by a factor of 4. If a method can be static, make a static declaration on it. The speed can

50 methods to skillfully optimize your SQL Server database There are many reasons for slow query speed. The following are common causes: 1. No index or no index is used (this is the most common problem of slow query and is a defect in programming) 2. Low I/O throughput, resulting in a bottleneck effect. 3. the

EXPLAIN to analyze queries, and adjusting the internal configuration of MySQL (the best combination with PHP) can optimize queries. Any database programmer has the following experience: a bad SQL query statement in a high-traffic database driver can seriously affect the running of the entire application, it not only consumes more database time, but also affect

SQL can be used to reorganise all indexes for the specified table; ALTER INDEX ALL ON While the following SQL can be used to reorganise a specific index. ALTER INDEX After organizing and/or rebuilding indexes, running the above SQL statement again will confirm the new fragmentation percentage (if any ). 8) Check table locks. if the source tables are locked due

Mysql explain usage (Optimize Query), mysqlexplain 　　ExplainIt shows how mysql uses indexes to process select statements and connect tables. Can HelpSelect a better IndexAndWrite more optimized query statements. 1. Create a database The created SQL statement is as follows: /* Navicat MySQL Data Transfer Source Server

Tags: structure execution character operator How SQL statements rebuild indexes insert TweenRecently, due to the need for work, we began to focus on the relative optimization of select query statements for MySQL databases.Because of the actual project involved, it is found that when the data volume of MySQL table reaches millions, the efficiency of normal SQL

In the optimization query, database applications (such as MySQL) mean the operation and use of the tool. Using indexes, using EXPLAIN to analyze and query, and adjusting the internal configuration of MySQL can optimize the query. Any database programmer has the following experience: MySQL with high traffic optimizes My

. On the first table, MySQL usually does a full table scan relative to the second table's subset of queries, which is part of the nested loop algorithm. 5. Optimize Union Using union across multiple different databases is an interesting optimization method, and union returns data from two unrelated tables, which means that no duplicate rows will occur and the data must be sorted, and we know that sorting is very resource-intensive, especially for larg

the same, but union all is not sorted. By the Internet: http://soft.chinabyte.com/database/254/11335754.shtml Reading Mysql optimization (how to query SQL statements with low execution efficiency in mysql) How can I perform joint queries on multiple tables with the same structure in a mysql database of tens of millions? How to optimize or set to improve the

Use sqlprofile to optimize slow SQL statements in goldengate and SQL statements In HP unix, a process is monitored to occupy a high cpu. Later, it was found that the statement execution plan in gg had a problem, resulting in a high cpu. Since the statements in gg cannot be changed, how can we change the execution plan of statements that cannot be changed? sqlprof

How to optimize QuerySet query by the select_related and prefetch_related functions of Django (1), djangoqueryset When the database has a foreign key, the use of select_related () and prefetch_related () can greatly reduce the number of database requests, thus improving performance. This article uses a simple example to explain the functions of these two functions. Although QuerySet has been described in de

How to optimize SQL statements (full) and SQL statements High-performance SQL statements play a very important role in software operation. The following section will share the latest SQL statement optimization materials. First:Select the most efficient table name sequence (o

, the 'qsoptimize _ province 'table has only two data entries: Hubei province and Guangdong province, and the 'qsoptimize _ City' table has only three data entries: Wuhan, Shiyan city, and Guangzhou city. Prefetch_related () You can use prefetch_related () to optimize multiple-to-many fields (ManyToManyField) and one-to-many fields. Maybe you will say that there is no such thing as OneToManyField. In fact, ForeignKey is a many-to-one field, and the fi

second table's subset of queries, which is part of the nested loop algorithm. 5. Optimize Union Using union across multiple different databases is an interesting optimization method, and union returns data from two unrelated tables, which means that no duplicate rows will occur and the data must be sorted, and we know that sorting is very resource-intensive, especially for large tables. Union ALL can be much faster if you already know that your data

Tags: MySQL sql slow log optimizerPrevious article: MySQL5.6 How to optimize SQL statements for slow queries--Slow log introductionIn the actual log analysis, usually slow log number of logs, while the same query is logged a lot of bars, here you need to find the most problematic from the slow log

Some methods for optimizing the query speed when mysql processes massive data bitsCN.com Recently, due to work needs, I began to pay attention to the optimization methods of select query statements for Mysql databases. In actual projects, it is found that when the data volume of the mysql table reaches the million level, the query efficiency of common