how to optimize sql query using execution plan

In programming languages with large numbers, the code is processed in the encoding order, but in SQL, the first clause to be processed is the FROM clause. Although the first SELECT statement appears, but it is almost always processed.Each step generates a virtual table, which is used as the input for the next step. These virtual tables are unavailable to callers (client applications or external queries. Only the table generated in the last step will b

, generating VT6. has: has a having filter applied to VT6. Only groups that make Select: processes the select list, producing VT8. DISTINCT: removes duplicate rows from VT8, resulting in VT9. ORDER BY: generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause. TOP: selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller. Note: Step 10, this

. Searching rows for update The qualifying records are being told to find out to prepare for the update. It must be completed before the UPDATE will modify the relevant records. Sleeping Waiting for client to send new request System Lock Is waiting to get an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by adding the--s

Tags: mysql optimized query execution processMySQL Query execution process The client sends a request to the server The server queries the cache, the cache hits the end, the results are returned to the client (the user's permissions are checked before returning), or the following steps continue The ser

[Preface] mysql can record the SQL statements executed by users: it can record the files and tables. mysql can define the execution time or longer. The SQL statements are slow queries, record related information to files and tables [background description] the company wants to monitor and record what SQL statements are

. Therefore, a bookmark lookup combined with a nonclustered index lookup makes sense because the query is selective. Now I'll execute the query with the 80000 parameter value. 1 --Execute the stored procedure 2 EXECRetrievecustomers1 3 GO 4 5 --Execute the stored procedure again 6 --This introduces now a plan regression, because now we get a Clustered Index Sca

the following: +-------------+--------------+ | customer_id | total_orders | +-------------+--------------+ | tx | 1 | | Baidu | 0 | +-------------+--------------+ You can see that this is the Total_orders column in descending order. The above results are stored in the VT8. 10. Execute the LIMIT clause LIMITClause selects the specified row data from the specified position from the VT8 virtual table obtained in the previous step. For a limit clause that doe

Enabling slow query log for tracking SQL Execution in MySQL and SQL log tracking in mysqlQuery slow query parametersShow variables like '% quer % '; Slow_query_log (whether to record slow queries) Slow_query_log_file (slow log file path) Long_query_time (logs

The author of the article gave great help to elder withered flourish, in order to express my gratitude.This applies to the Oracle DB version number, Oracle 10g, or later.It is important to say that this view is one of the AWRSQRPT reports is not in. is the filter_predicates column.SELECT Plan_hash_value,To_char (Rawtohex (child_address)), To_number (child_number), ID, Lpad (", DEPTH) | | Operation operation, Options, Object_owner, object_name, optimizer, cost, Access_predi Cates, filter_predicat

. And many developers like to use this statement to solve paging problems. For small data, there is no problem with the limit clause, and when the amount of data is very large, using limit N, M is very inefficient. Because the mechanism of limit is to scan from scratch every time, if need to start from the No. 600000 line, read 3 data, you need to scan to locate 600,000 rows before reading, and the scanning process is a very inefficient process. There

clause is used with the ORDER BY clause. The MySQL database limit supports the following forms of selection: LIMIT N, M Indicates that the M record is selected starting with the nth record. And many developers like to use this statement to solve paging problems. For small data, using the LIMIT clause has no problem, and when the amount of data is very large, the use LIMIT n, m is very inefficient. Because the mechanism of limit is to scan from scratc

Using a maintenance plan for SQL Server 2008 enables you to automatically back up the database and automatically remove the functionality of outdated backups.First, the environmentOs:microsoft Windows Server 2003 R2Soft:microsoft SQL Server 2008Backing up the database path C:\sql_bakSecond, the configurationLogin Micro

First look at the ARENA_MATCH_INDEX table structure, you notice the index structure of the table Copy Code code as follows: CREATE TABLE ' Arena_match_index ' ( ' Tid ' int (a) unsigned not NULL DEFAULT ' 0 ', ' Mid ' int (a) unsigned not NULL DEFAULT ' 0 ', ' Group ' int unsigned not NULL DEFAULT ' 0 ', ' Round ' tinyint (3) unsigned not NULL DEFAULT ' 0 ', ' Day ' date is not NULL DEFAULT ' 0000-00-00 ', ' BeginTime ' datetime not NULL DEFAULT ' 0000-00-00 00:00:00 '

Tags: SQL SELECT statement Efficient SQL execution orderThe SELECT query statement is the most commonly used SQL statement, and the following is the order in which the statements of select are executed, and you can refer to the order of