using index condition using filesort

http://blog.itpub.net/22664653/viewspace-1210844/--This blog is written in a finer, later lookICP (index condition pushdown) is an optimization of MySQL's use of index (level two index) tuples and sieve fields to extract data records from a table in the Where condition in th

Tags: presence return name Zed like contains improved distinct algorithmusing FilesortThe average person's answer is, "when the row data is too large to allow the memory to tolerate temporary tables generated by the data, they are placed on the disk to sort." Unfortunately, the answer is wrong, and the temp table does go to disk when it's too big, but explain doesn't show it.The truth is, Filesort is badly named. Anytime a sort can ' t is performed fr

still need to sort it by the created field in the post table, that is to say, the sorting field is not in the driver table, so "Using filesort" and even "Using temporary" are inevitable 」. Knowing the ins and outs makes optimization easy. To ensure that the sorting fields are in the driving table as much as possible, you must use post as the driving table. There

searching to read the actual rows. Usingtemporary in order to resolve the query, MySQL needs to create a temporary table to accommodate the results. In the Type field ref for each row from the preceding table, all rows with matching index values will read from this table that all is completely non-indexed, and the performance is very poor. Index is the same as all except that only the

Explain select UserID from Team_enewsmember where Agentid in (select UserID from Team_enewsmember where agentid= ' 2 ' and Lev Elid>=1 GROUP by UserID) and Levelid>=1 GROUP by UserID The Using where is present if the primary key UserID and the Agentid Federated Index. Using Index Why not go away with the

SQL query optimizes the where condition sorting fields and the mysteries of using the index by limit.Strange slow SQL Let's take a look at two SQL statements.Article 1: Select * from acct_trans_log WHERE acct_id = 1000000000009000757 order by create_time desc limit 1000000000009003061 second: select * from acct_trans_log WHERE acct_id = order by create_time desc

From: http://hi.baidu.com/anson7722/blog/item/dd0f5c02357f5b024afb51ba.html Explain is a keyword used by MySQL to explain select queries and can be easily used for debugging.Syntax format:Explain tbl_nameOr:Explain select select_optionsThe explain statement can be used as a synonym for describe, or used to obtain information about the SELECT statement to be executed by MySQL. The explain tbl_name syntax is the same as describe tbl_name or show columns from tbl_name. When the keyword "Explain" is

When you use explain to analyze SQL statements, one of the values of extra is using filesort, for example: Primary Key ('id '),Key 'uid' ('uid ') Explain select * From t_talbe where uid = 1 order by ID; Extra result: Using where; using filesort In the manual,

Syntax format:EXPLAIN tbl_nameOr:Explain select select_optionsThe EXPLAIN statement can be used as a synonym for DESCRIBE, or used to obtain information about the SELECT statement to be executed by MySQL.The EXPLAIN tbl_name syntax is the same as DESCRIBE tbl_name or show columns from tbl_name.When the keyword "EXPLAIN" is used before a SELECT statement, MYSQL explains how to run the SELECT statement, and displays the table connection and connection sequence.The following information is a refere

Using filesort caused by order by optimization by MySQL Using filesort is generally used in the order by statement. Using filesort does not necessarily cause mysql performance problems. However, if the number of queries is very la

who is the driver table, which is the table, and when dealing with such problems, the MySQL optimizer uses a simple and rude workaround: Which table has a small result set, which table is the driver table, Of course, the MySQL optimizer actually handles a lot more complex, for reference: How the MySQL optimizer chooses the index and join order. Note: In the explain result, the table that appears in the first row is the driver table. To continue the P

http://blog.csdn.net/yangyu112654374/article/details/4251624such as an SQL statementSELECT * from TABLE1 where a1=1 and a2=2 and a3=3 order by A4 ASC;After optimization: Select A4,a1,a2 ... From TABLE1 where A1=1 and a2=2 and a3=3 order by A4 ASC; And index the A4,A1,A2,A3, when using order by A4, the Federated index should put A4 in the first place.The order by

The original JOIN Connection statementSELECT SUM(Video_flowers. Number) as Num,VIDEO_FLOWERS.FLOWER_ID,Flowers.title,Flowers.image from' Video_flowers ' JOIN' Flowers ' on ' Video_flowers '.' flower_id '=' Flowers '.' id ' JOIN' video_posts ' on ' Video_flowers '.' video_post_id '=' video_posts '.' id 'WHERE' video_posts '.' user_id '= $GROUP by ' Video_flowers '.' flower_id ' can be optimized intoSELECT Vf.Num, Flowers.title, Flowers.image from ' Flowers ' Join(SE

In MySQL, there are actually two filesort implementation algorithms. One is to first retrieve the corresponding sorting fields based on the corresponding conditions and the row pointer information that can be directly located in the row data, then sort in sort buffer. The other is to retrieve all fields that satisfy the condition row at a time and sort them in sort buffer. Only the first sorting algorithm

The argument is that an or is present in the Where condition and the index is not used My test results are as follows: Mysql> explain select * from emp where ename= "Dsleos" or ename= "Saesad" \g*************************** 1. Row *************************** id:1 select_type:simple table:emp type:rangepossible_keys:e_i key:e_i key_len:62 ref:null

Tag: The user using ROM uses the RAC statement from the ORDER by nbspThe solution to this problem is that MySQL can use only one index per query, and your SQL statement's WHERE condition is not the same as the condition of ORDER by, and the index is not built. Then the ORDER

Label:This problem occurs because MySQL can use only one index per query, and your SQL statement WHERE condition and order by conditions are not the same, the index is not built, then the ORDER by is not used to index, there is a using f