subquery vs join performance mysql

variable. So all you need to do is use a variable instead of the MySQL function to turn on the cache.2. EXPLAIN your SELECT queryUse the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query statement or table structure.EXPLAIN's query results will also tell you how your index p

numeric data, and numeric data is processed much faster than text type. In this way, we can improve the performance of the database. 2, use the connection (join) to replace the subquery (sub-queries) MySQL supports SQL subquery starting with 4.1. This technique can use a

At the early stage of application system development, due to the relatively small amount of data in the development database, the performance of SQL statement writing is not good for querying SQL statements and writing complex views, however, after the application system is submitted to the actual application, as the data in the database increases, the system response speed becomes one of the most important problems to be solved by the system. An impo

query process will lead to a large number of random I/O generation. In this scenario, unless the index can be well overwritten, the data table must be queried Based on the index, the query performance is greatly affected. In this case, you can use the Partition Table policy to improve the query performance. Different database management systems may have different implementations for partitions. This articl

When the data volume is very large (the table capacity reaches GB or TB), if you still use the index method to optimize the query, due to the consumption of the index and the generation of a large number of index fragments, the query process will lead to a large number of random I/O generation. In this scenario, unless the index can be well overwritten, the data table must be queried Based on the index, the query performance is greatly affected. In th

. mysql is estimated by statistical data to be 31156, that is, all the satisfied group_id= returned by the User_group table 1 record count (actual data in the system is 20000). While the second solution's execution plan, the user table participates in the join the data to have only 20, the difference is very big, we think the second solution should be obviously superior to the first one solution. 4 schema

data; Basic information data of active users; Personalized and customized information data for active users; Quasi-real-time statistical information data; Some other frequently accessed but less-changed data; 3 Query The effect of statements on system performance Requirements: Take out a group (assuming ID 1) under the user number (ID), user nickname (Nick_name), and follow the time to join the group (user

this. Then, you'll see a table. In the following example, we forget to add the group_id index and have a table join: When we index the group_id field: As we can see, the previous result shows a search of 7883 rows, and the second one searches only 9 and 16 rows of two tables. Looking at the rows column allows us to find potential performance issues.3. Use LIMIT 1 when only one row of data is us

MySQL database performance optimization-table structure optimization bitsCN.com Many As a database table structure design "Bible", the author believes that as long as the design is based on this paradigm requirement, the designed table structure can be optimized enough to ensure excellent performance and meet scalability requirements. It is unknown that th

to do this. Then, you'll see a table. In the following example, we forget to add the group_id index and have a table join: ww.phperz.com When we index the group_id field: As we can see, the previous result shows a search of 7883 rows, and the second one searches only 9 and 16 rows of two tables. Looking at the rows column allows us to find potential performance issues. 3. Use LIMIT1 when only one row of

; show global status like 'select _ range ';+ --------------- + ---------- +| Variable_name | Value |+ --------------- + ---------- +| Select_range | 22450380 |+ --------------- + ---------- +1 row in set (0.00 sec) This value indicates the number of join statements using ranges in the first table. A large value indicates that join is not written properly. Generally, you can check the ratio of select_full_j

upgrade the infrastructure when the results are achieved through simple MySQL optimization. To do a good job, you must first sharpen the tool. here we will first introduce the MySQL Query statement performance analysis tool. The MySQL EXPLAIN command is used to analyze the query

(key:user_group_gid_ind), the user table takes advantage of the primary key index (key:PRIMARY), before participating in the join, MySQL joins with the user table through the Where filtered result set, and finally by sorting outThe "limit 100,20" result of the Join results in a return.Solution two SQL statements take advantage of subqueries, so the execution pla