subquery vs join performance mysql

a "permanent link," you need to consider the architecture of your entire system. 19, when the query is slow, you can use join to rewrite the query to optimize Mysql> Select Sql_no_cache * from Guang_deal_outs where deal_id into (select ID from guang_deals where id = 100017151); Empty Set (18.87 sec) mysql> select Sql_no_cache a.* from guang_deal_outs

sorted by primary key 2,set unique_checks=0, turn off uniqueness checksum. 3,set autocommit=0, turn off automatic submission. Optimize GROUP BY statement By default, MySQL sorts all group by Col1,col2. This is similar to the col2 specified in the query by Col1. If the query includes group by but the user wants to avoid the sort result consumption, you can use the order by Null prohibit sort In some cases, you can use a connection instead of a

numeric data is processed much faster than the text type. In this way, we can improve the database performance. 　　2. Use JOIN instead of Sub-Queries) MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want

. 2, use the connection (join) to replace the subquery (sub-queries) MySQL supports SQL subquery starting with 4.1. This technique can use a SELECT statement to create a single column of query results, and then use the result as a filter condition in another query. For example, to delete a customer with no orders in th

The following article mainly introduces several practical operation solutions for improving MySQL database performance, including selecting the most suitable field attribute and correctly using JOIN) subquery (Sub-Queries. 1. select the most suitable field attribute MySQL ca

Mysql performance optimization, mysql 1. When using an index to query records, you must pay attention to the index usage, for example: (1) The string configured with the like keyword cannot start with '%' (2) When using multi-column indexes, the first field of the index must be used as the query condition. (3) When the or keyword is used, indexes must be used

Tags: end contains execution plan processing tab tin Retrieving a federated query operationExplain: Use the Explain keyword to simulate the optimizer executing SQL query statements to know how MySQL handles your SQL statements. Analyze the performance bottlenecks of your query statements or table structures expain+sql Statements the sequence number of a id:select query that contains a set of numbers that in

Tags: style blog http using strong data SP 2014 problemFirst, why do we have this experiment Our system is a batch processing system, similar to the architecture of a pipeline. Each data table is the end of the pipeline, and our program is similar to the pipeline itself. All we need to do is to extract the data from a table, through a certain filtering, summary and other operations placed in the B table. If there is an error, then run the pipeline again. Therefore, our system actually does not h

as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance. 2. Use JOIN instead of Sub-Queries) MySQL supports SQL subqueries from 4.1. This technique

SELECT query Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze your query statements or the performance bottlenecks of the table structure. EXPLAIN's query results will also tell you how your index keys are being used, how your data tables are searched and sorted ... Wait, wait. Pick one of your SELECT statements (recommend picking the most complex,

the join to rewrite the query to optimizeMysql>Select Sql_no_cache *From Guang_deal_outswhere deal_idIn (Select IDFrom Guang_dealsWHERE id =100017151);EmptySet (18.87 sec) mysql>Select Sql_no_cache a.*From Guang_deal_outs a innerJoin Guang_deals b on a.deal_id = b.IDwhere b.id =100017151;EmptySet (0.01 sec) Reason mysql> descSelect Sql_no_cache *From Guang_deal_

during future queries. For some text fields, such as "Province" or "gender", we can define them as the ENUM type. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance. MySQL database performance

[SQL] MySQL Performance Optimization bitsCN.com [SQL] MySQL Performance Optimization 1. optimize your query for the query cache Query cache is enabled on most MySQL servers. This is one of the most effective ways to improve performance