Mysql parse out the implementation plan-Extra

2011-02-22  来源:本站原创  分类:Database  人气:64 

Mysql implementation plan, other than a good understanding of columns, except the Extra column, people always see a little half-comprehended, before finally found a chance to study under his.

using index:

Chinese name is called the index covers the query: if the query time, use the index, and the data you need is the final part of this index, then there using index.

For example:
user table has an index key (id), key (name)

select id from user;
select name from user;
select id from user where id <9;

The select id, name from user where id <9 no

Composite index key (id, name)
select id, name from user where id> 9 can have

using where:
Based on the query to filter the result set

using file sort and using temporary:
These two are the most likely confusing, many people are likely to extend the meaning of the expression will understand, in fact, the content of his expression is very limited.

using file sort: that sort of time, no access to the index, had to take other sort of way. Here are other ways in memory row, row in a temporary file, using the dual sort, or use the entire line sorting, and using file sort and did not say what sort of other methods.

using temporary: that uses a temporary table, as this temporary table in memory or on disk which are unknown.

Emergence of this situation is very much a two-step, here only a few columns it:

using file sort:
order by the first column is not the first implementation plan in the index on the table
order by multiple columns, not in the same index on the table
order by the column, there are ascending, descending, inconsistent

using temporary:
In the group by time, if, by group by column is not indexed, then you need a temporary table for group, the other without order by null, may have to file sort order.
In order by time, if the sort column, distributed in excess on a table

相关文章
  • Mysql parse out the implementation plan-Extra 2011-02-22

    Mysql implementation plan, other than a good understanding of columns, except the Extra column, people always see a little half-comprehended, before finally found a chance to study under his. using index: Chinese name is called the index covers the q

  • MySQL hot backup implementation plan 2010-12-27

    MySQL hot backup implementation plan 1, MySQL database does not incremental backup mechanism, when the data is too big when the backup is a big problem. Fortunately, the main MySQL database provides a mechanism from a backup, in fact, the master data

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • Implementation Plan (Execution Plan) process analysis 2011-01-11

    Simply put, the implementation plan is actually constituted by a series of data processing functions of a tree chain; those who have access to the physical data of the function is called the access path (Access Path, such as the Index Range Scan, Ful

  • Oracle Implementation Plan (Explain Plan) Description 2011-07-07

    http://blog.csdn.net/tianlesoftware/article/details/5827245 http://blog.csdn.net/tianlesoftware/article/details/5826546 If you want to analyze the SQL performance on a particular issue, usually we have to look at the SQL execution plan to see each st

  • Oracle: implementation plan: access path connection table, suggesting Statistical information bind variables 2011-09-09

    SQL execution: Analysis: Hard analysis, soft analysis Implementation Plan: access path (access path), table join (table join), statistics (statistics), bind variable (bind variable), tips (hints) ================================================== ===

  • Oracle Implementation Plan 2010-03-09

    Summary: SQLPLUS under the automatic display, look at their statements or implementation plan will be implemented. Especially in the implementation of the UPDATE / DELETE statement, please do note, ORACLE is the first implementation of the script als

  • Read oracle's implementation plan 2010-04-28

    Read oracle's implementation plan Implementation plan for beginners is usually more difficult to read, often reading the implementation plan from the inside out, starting from the operation of the deepest indentation. General principles: 1. First of

  • MapReduce implementation plan to use some algorithm [Translation] 2010-05-07

    MapReduce implementation plan to use some algorithm With the growing size of treatment plan (such as complex network), which map the nodes and the side information can not be completely loaded into memory, which to implement the algorithm in the map

  • Analysis of the implementation plan optimization SQL <2> ORACLE optimizer (change) 2010-05-27

    Optimizer is sometimes called the query optimizer, which is affecting database query performance because the most important part, do not think that only the SELECT statement is the query. In fact, with any WHERE conditions DML (INSERT, UPDATE, DELETE

  • oraclesqlplus implementation plan 2010-06-17

    General can be seen through the many tools PL / SQL statement execution plan to analyze the performance. Here through the PL / SQL view sql implementation plan in several ways: Method 1. Set AutoTrace on; Then when your sql statement execution time,

  • Implementation plan to get through dbms_xplan.display_cursor 2010-08-04

    Used to view the implementation plan for the implementation of the sql: SQL> select count (*) from abc; COUNT (*) ---------- 10 SQL> select sql_id from v $ sql where sql_text = 'select count (*) from abc'; SQL_ID ------------- crrfjnb0y4mq1 SQL>

  • Like an index in MySQL Full Text Search Implementation 2010-08-15

    Like an index in MySQL Full Text Search Implementation * Abstract: In the real business, it is hard to avoid the MySQL full-text search and indexing Like this demand. Such as fuzzy search user accounts, nicknames and the like. This article shows you

  • Oracle SQL with AutoTRACE analysis of the implementation plan 2010-09-06

    Turn http://hi.baidu.com/bystander1983/blog/item/d6c26a53fe457b040cf3e3cd.html * Environment: windowsXP + Oracle10gR2 * AutoTRACE is to analyze the SQL execution plan, the efficiency of a very simple and convenient tool * / AUTOTRACE is a function of

  • [Sql tuning of the implementation plan] merge semi join and merge anti join 2010-10-27

    [Sql tuning of the implementation plan] merge semi join and merge anti join Semi Join (also called half-connections) or more in the sub-query in the use of such exists, for the outer row set, look for the internal (ie, sub-query) row set, match back

  • scheduled task php php implementation plan tasks in ignore_user_abort 2010-12-09

    Function-ignore_user_abort, this function can help us to achieve the same as the linux implementation plan in the cron task, under the following terms with how to achieve. First have a look on the php manual explains the function Description int igno

  • How to see Oracle implementation plan 2011-01-14

    oracle explain an implementation plan. Related Concepts 1 · rowid, pseudo-column: is the system to add their own, each table has a pseudo-column is not physically exist. It can not be modified, deleted, and added, rowid in the life cycle of the line

  • Oracle Implementation Plan Detailed 2011-05-06

    Oracle Implementation Plan Detailed --- Of: TTT BLOG This article addresses: http://blog.chinaunix.net/u3/107265/showart_2192657.html --- Description: Full details of this oracle concepts related to the implementation plan, access to the data access

  • Why Oracle will sometimes use the index to find data? - Force Oracle to use the best "implementation plan" 2011-06-01

    [Abstract] you use SQL, a query to the database when you release, Oracle will be generated with an "implementation plan" that is, what kind of data that the statement will search through the implementation of the program. Search for program sele

  • Detailed implementation plan 2011-03-09

    First, what is the implementation plan An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Second, how to access data At the physical level Oracle reads blocks of data. The smallest amount of d