subquery vs join performance mysql

contains MAX (), when using the MIN () and ORDERBY commands, the performance improvement is more obvious. Which fields should be indexed? In general, the index should be built on the fields that will be used for JOIN, WHERE judgment and order by sorting. Try not to index a field in the database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of

contains MAX (), when using the MIN () and ORDERBY commands, the performance improvement is more obvious. Which fields should be indexed? In general, the index should be built on the fields that will be used for JOIN, WHERE judgment and order by sorting. Try not to index a field in the database that contains a large number of repeated values. For an ENUM type field, it is very likely that a large number of

joins) and add the keyword explain to the front. You can use phpMyAdmin 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

necessarily give the primary key or the unique field. If you have a field in your table that you will always use to do a search, then index it. From you can see that search string "last_name like ' a% '", one is built index, one is no index, performance is about 4 times times worse.In addition, you should also need to know what kind of search is not able to use the normal index. For example, when you need to search for a word in a large article, suc

find out if there are users of "China", it is obvious that the latter will be more efficient than the previous one. (Note that the first one is select *, and the second is select 1)4. Jianjian Index for search wordsThe index does not necessarily give the primary key or the unique field. If you have a field in your table that you will always use to do a search, then index it.From you can see that search string "last_name like ' a% '", one is built index, one is no index,

concurrency of the query although also produce negative At least within a controllable range; The principle of JOIN is ' small result set driving large result set ': A table 1000 data, b table 100,000 data SELECT a.*, b.name from a left JOIN B on a.id=b.a_id//Here is a table as the driver table Loop Connection B table Reduce the number of cycles; b represents the driven table; Explain SQL stat

performance, because the database will continue to read and write these useless blocks on each INSERT, and squeeze out important blocks of memory. To avoid these problems, you need to take some complex measures (for example, setting a new index on the Slave node and then performing the opposite operation between the slave node and the master node ), however, these measures may cause errors and are difficult to implement. they impede the new functions

(say, search for keywords or tags, etc.)5. Use a fairly typed example in the Join table and index itIf your application has many join queries, you should confirm that the fields of join in two tables are indexed. In this way, MySQL internally initiates the mechanism for you to optimize the SQL statement for join.Also,

or make an index yourself (say, search for keywords or tags, etc.)5. Use a fairly typed example in the Join table and index itIf your application has many join queries, you should confirm that the fields of join in two tables are indexed. In this way, MySQL internally initiates the mechanism for you to optimize the SQ

Label:Today, database operations are increasingly becoming a performance bottleneck for the entire application, which is especially noticeable for web applications. It's not just about the performance of the database that DBAs need to worry about, it's something that our programmers need to focus on. When we design the database table structure, we need to pay attention to the

through the application. Of course, the requirement for this is that our Russian application must be robust enough to provide some technical difficulties for the application as well.2. Cross-node Join issuesIt is recommended that the application be processed by taking the corresponding drive result set in the MySQLServer where the driver table is located, and then taking the corresponding data from the drive result set to the

; Personalized customized information data for active users; Quasi-real-time statistical information data; Other data that is frequently accessed but with less change; 3 The effect of query statement on system performance Requirements: Take out the user number (ID) under a group (assuming ID 1), the user nickname (Nick_name), and follow the group Time (user_group.gmt_create) to reverse order, taking out the first 20. Solution One: Copy C

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:650) this.width=650; "border=" 0 "src=" http://files.jb51.net/upload/201007/20100727224417501.jpg "alt=" 20100727224417501.jpg "/>When we index the group_id field:650) this.width=650; "border=" 0 "src=" http://files.jb51.net/upload/201007/20100727224427186.jpg "alt=" 20100727224427186.jpg "/>As we can see, the previous result shows a

This article describes how to use the showprofile command in MySQL to analyze performance. showprofiles is a common command for database performance optimization, for more information, see how to donate the show profile to the MySQL community by Jeremy Cole. This feature is disabled by default, but can be enabled at th

typed column in the Join table and index it ⑥ do not ORDER by RAND () ⑦ Avoid select *⑧ always set a id⑨ for each table you can use enum instead of Varch Ar⑩ use not NULL as much as possible? is a fixed-length table faster? Split vertically? Split a hit delete or INSERT statement? The smaller the column the faster? Choose the right storage engine? Careful "permalink" is described as follows: (i) query cache optimization queries most

This article introduces 10 MySQL Performance tuning methods. each method is very detailed and practical. For more information, see MYSQL, which is the most popular WEB backend database. WEB development languages have developed rapidly recently. PHP, Ruby, Python, and Java have their own characteristics. although NOSQL has been mentioned more recently, I believe m

This article summarizes and introduces MySQL Performance Optimization in detail. For more information, see VII. performance optimization of MySQL database Schema design Efficient model design Moderate redundancy-reduce Join in two queries Vertical split of large fields-sum

Analysis of performance differences before and after a user migrates a database: A User's ticket: data is migrated from ECS to RDS. with the same statements, the query performance is reduced by dozens of times. In fact, the memory configuration of the RDS instance is equivalent to that of the original ECS instance. This article briefly describes the causes and suggestions for this case. The statement for s