subquery vs join performance mysql

|| Handler_read_rnd | 219230406 || Handler_read_rnd_next | 344713226172 |+-----------------------+--------------+7 rows in set (0.00 sec) Handler_read_first: Number of times the index is used for scanning. This value is not clear about big novels. the system performance is good or bad. Handler_read_key: number of queries by key. A greater value indicates better system performance. Handler_read_next: Th

Selection of sequence and access mode: exhaustive MySQL can find the optimal execution sequence and access method by enumerating all left-deep trees (or all left-deep trees are the search space of the entire MySQL Optimizer.2.3.1 sorting The optimizer first sorts all tables according to the found records, and places the first few records. Therefore, the order here is B and.2.3.2 greedy search When the numb

Http://www.linuxidc.com/Linux/2015-05/117523.htm 1. Background informationWhat is Semi-join?The so-called Semi-join refers to the semi-join subquery. When a table finds a matching record in another table, the semi-join (semi-jion) returns the records from the first table. In

: mysql> show global status like ‘com_select‘;+—————+———–+| Variable_name | Value　　　　　 |+—————+———–+| Com_select　　　　 | 222693559 |+—————+———–+ Calculate the table scan rate:Table scan rate = Handler_read_rnd_next/Com_selectIf the scanning rate of a table exceeds 4000, too many table scans are performed. It is very likely that the index has not been created. Increasing the value of read_buffer_size may be advantageous, but it is best not to exceed 8 MB

identifier. This is the serial number of the SELECT query. Id example SELECT * FROM emp WHERE empno = 1 and ename = (SELECT ename FROM emp WHERE empno = 100001) \G; Select_type PRIMARY: The oldest query in the subquery SUBQUERY: The first SELECT in the SUBQUERY layer. The results do not depend on external queries. Dependent

The auto-join processing speed of mysql is much faster than that of subqueries. Therefore, it is necessary to learn self-join. SELECT 'id', 'bic' FROM 'biao' WHERE 'id' (SELECT 'id' FROM 'biao' WHERE 'id' 9696e) in a common SQL subquery statement ); mysql self-

This article mainly introduces the simple comparison of the three associated query methods in MySQL, that is, ON and USING, and the traditional FROM... WHERE ..., for more information, see the following SQL statements for the three associated queries? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)SELECT * FROM film JOIN film_actor

must be added for this data clearing operation, that is, delete A from A where... is followed by A condition. The batch deletion method is no longer effective.I wonder why. Mysql not in and left join efficiency issue recordsThe function of this SQL statement is to query data in which set a is not in Set B.Not in statementCopy codeThe Code is as follows: select add_tb.RUIDFrom (select distinct RUIDFrom User

subquery | UserMsg | index_subquery | RUID, SubjectID, CreateTime | RUID | 96 | func | 2 |Using index; Using where || 2 | DERIVED | UserMsg | range | SubjectID, CreateTime | 9 | NULL | 1857 |Using where; Using temporary |+ ---- + -------------------- + ------------ + ------------------ + ------------------------- + ------------ + --------- + ------ + ------------------------------ +Analysis: the reason for the fast query speed is that the SQL query r

website, which I do not understand: If 10 rows are cached in the buffer, these 10 rows are passed to the inner loop, all rows in the inner loop are compared with the 10 rows in the buffer. The original article is as follows: For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the bufferIf S refers to the size of t1 and t2 combinations in the cache, and C is the number of these comb

for each unique open table The file descriptor. The default value is 64, and I'll change it to 512. (8) Thread_cache_size:The number of threads stored in that can be reused. If there is, a new thread is obtained from the cache, and when disconnected, if there is room, the customer's line Placed in the cache. If there are a lot of new threads, in order to improve performance you can have this variable value. By comparing connections and threads_cre

greatly improved. Join buffer variable join_buffer_size from the previous section you can find that join buffer is an optimization to reduce the number of internal table scans, but join buffer is not as simple as In the previous section, inside deliberately overlooked some implementations. First, variable join_buffer_size is used to control the size of the

main table's handsome selection, and the main table or the whole table. (2): The filter criteria for the primary table should be placed behind the where, should not be placed behind (3): We have to differentiate between related tables. If you want to condition query after the connection should be the query pieces Placed after on. If you want to filter after the connection is complete, you should put the condition behind the where (4): for the association table we can actually do the

query results are retained in the join buffer. The size of the column values does not depend on the table size. We can see in the pseudo code that when the join buffer is filled up, mysql will flush buffer.Suppose we want to retrieve all users without order records, we can use the following query to complete: The code is as follows:Copy code SELECT * FROM cu

There's a problem with this MySQL query uses the right join filter in MySQL to see if there is a discrepancy between on and where the data is queried. Maybe just look at two keywords and see no problem. So we use actual examples to see if there's any difference. For example, there are two table structuresTable Structure 1 Copy Code code as follows:

the performance point of view the second is obviously more time-saving. 4: Note the difference between the ON clause and the WHERE clause The code is as follows: SELECT * FROM product left JOIN Product_details On (product.id = product_details.id) and product_details.id=2; SELECT * FROM product left JOIN Product_details On (product.id = product_details.id) WHERE

Union and join is the need to unite more than one table common related words, the specific concept I do not say, want to know online search on the line, because I also remember inaccurate.First of all, the difference: union the operation of two tables is to combine the number of data bars, which is equal to the portrait, which requires that both table fields must be the same (Schema of both sides of Union should match.). In other words, if there are t

This article mainly introduces the distinct statement in MySQL to query duplicate records and related performance discussions. the point in this article is to avoid using distinct in top-level queries under certain circumstances, for more information, see MySQL Query. duplicate values may be included. This is not a problem. However, sometimes you may want to list

In MySQL query, sometimes the business complex, have to write multi-table query, many of the early apes like to write subqueries, because the subquery is easy to understand, not prone to problems, but the efficiency of sub-query is not as related to query; Association queries I think it is a double-edged sword, in the mechanism does not understand it, or use caution, I met the former colleague, because the

:1Select_type:simpletable:mType:refPossible_keys:primary,idx_group_message_gid_uidKey:idx_group_message_gid_uidKey_len:4ref:example.g.group_idRows:3Extra:*************************** 3. Row ***************************id:1Select_type:simpleTable:cType:allPossible_keys:nullKey:nullKey_len:nullRef:nullrows:96extra:using where; Using Join BufferWe see that not only the access to the User_group table has changed from ref to all, but also that the extra info