subquery vs join performance mysql

Tags: connect database recommended back multithreading ons ali new passwd tryMySQL server will actively disconnect this connection if it has no access request within 8 hours. When you use Pymysql or MYSQLDB to manipulate a database connection, the connection pool is occupied when the cursor is in a connected state and is not close in time. To view the background log:"MySQL server has gone away (%r)"% (e,)) Pymysql.err.OperationalError: (2006, "

SELECT * from ASELECT * from BThese are the two-sheet bodies. SELECT * from Left JOIN on a.ID=b.a_id;statement One : SELECT*from leftjoins on a.id=and 3;Statement two: SELECT*from leftjoins on a.id=WHERE 3;The results of the two-statement query are different.The keyword order for SQL queries is from, where, group by-> have->order byThe left join is in the from range so first on cond

When using ResultSet to return a result set, we may only need the first 10, some people will naively think that the application is a loop, take a piece of data, we close the result set, we will not continue to query. MySQL does not actually do this, but it will all be detected, and then there is memory. In this case we'd better use limit to intercept the data. Returns all columns when multiple table associations

only one row of data2. Avoid select *, always specify the columns you needThe more data is read from the table, the more slowly the query becomes. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers.3. Use connection (join) instead of

| Key | Key_len |Ref | Rows | Extra | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+---- ---------+ |1 | Simple | B | Const | PRIMARY | PRIMARY | 4 | Const | 1 | Using Index | | 1 | Simple | A | Ref | idx_guang_dlout_dlid | idx_guang_dlout_dlid | 4 | Const | 1 | | +----+-------------+-------+-------+----------------------+----------------------+---------+-------+---- --+-------------+ 2 rows in set (0.05 sec) In fact, guang_d

data is processed much faster than text types. In this way, we can improve the performance of the database.2. Use connection (join) instead of subquery (sub-queries)MySQL supports SQL subquery starting from 4.1. This technique can use the SELECT statement to create a single

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements. Using the method, add explain to the SELECT statement.I. Explanation of the Explain column1, id:select identifier. This is the Select query serial number. This is not important, the query sequence is the SQL statement execution order;2, select Type, it

High-Performance MySql evolution (11): Optimization of common query statements bitsCN.com Summarize the optimization methods of common query statements.1 COUNT 1. Functions of COUNT · COUNT (table. filed) indicates the number of records for which this field is not null. · COUNT (*) or COUNT (not nullable field) indicates the number of rows in the entire table. If you COUNT the number of records in the entir

the time being, wait until the performance test to say again)3, fully connected full join, this and the above slightly different is that the syntax is not used behind the join on, the direct Select field name from the tablename1 full join tablename2, and its results will also appear before the Cartesian product phenom

of fields in the SELECT clause does not affect the read data?Yes, most of the time it doesn't affect the IO amount, but when we still have an order by operation, the number of fields in the SELECT clause affects our sorting efficiency to a large extent, which is more detailed in my previous article about the implementation analysis of the MySQL orders by. A fine introduction.In addition, the above misunderstanding is not also said, but most of the ti

NULL values during future queries.Some text fields, such 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 performance of the MySQL database.2. Use JOIN instead o

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 to delete a customer who has no orders in the basic customer information table, you can use the

and Post_ta g.tag_id = 123 ORDER by post.created DESC LIMIT 100 Try using explain to query the SQL Execution plan (limited in length, with the result being truncated): +----------+----------------+--------+-------------+ | table | key | rows | Extra | +----------+----------------+--------+-------------+ | post | status_created | 119340 | Using where | | Post_tag | post_id | 1 | Using where | +----------+----------------+--------+-------------+ Compared to t

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 to delete a customer who has no orders in the basic customer information table, you can use the

Label:13th. The extensible design of MySQL Replication Objective: MySQL Replication is a very distinctive feature of MySQL, and he is able to copy the data from one MySQL server Instance to the Instance of another MySQL server. Although the replication process is not real-ti

VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields. Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries. Some text fields, such as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric

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

query statement or table structure.The EXPLAIN query results also show you how your index primary key is used and how your data tables are searched and sorted ...... And so on.SELECT one of your SELECT statements (we recommend that you SELECT the most complex one with multi-table join) and add the keyword "EXPLAIN" to the front. You can use phpmyadmin to do this. Then, you will see a table. In the following example, we forget to add the group_id inde

the same query in the future without having to operate the table. The main problem here is that this is easy for programmers to ignore. Because some of our query statements will make MySQL not use cache. See the following example: The difference between the preceding two SQL statements is CURDATE (). The query cache of MySQL does not work for this function. Therefore, SQL functions such as NOW (), RAND ()

set the field width in the table as small as possible to achieve better performance. For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields. Another way to improve efficiency is to set the field t