mysql query performance tuning

source data recorded in a table by primary key identification Get the value of the field to distinct to get the final result. In the three steps above, the most consumed performance is the second step. Because the actual data of the index and the table is actually placed separately, probably looks like the following figure. The largest one in the figure is actually a data table, all the data in the table is on it, but it doesn't look li

Describes how to use mysql Performance query. Everything comes from an experiment. See the following example: Table: Copy the code: CREATETABLEIFNOTEXISTS 'foo' ('A' int (10) unsignedNOTNULLAUTO_INCREMENT, 'B' int (10) u everything comes from an experiment. please refer to the following example: Table: The code is as follows: Create table if not exists 'foo '('

In a MySQL query, duplicate values may be included. This is not a problem, but sometimes you may want to list only the different values (distinct). Keyword DISTINCT is used to return only a different value, that is, to go heavy. The usage is also very simple: SELECT DISTINCT * FROM TableName DISTINCT this keyword to filter out the extra duplicates and keep only one. In addition, if you want

When you want to optimize query performance, the best way is to figure out how MySQL optimizes and executes the query. Understand the internal mechanism, in order to better implement the design. When MySQL executes a query, what

Php + mysql prequery prepare and general query performance comparison, mysqlprepare Prepare can solve the load and overhead brought by a high-traffic website to the database server. This article introduces the performance comparison between prepare and common queries through examples, for more information, see. The ins

calculations, generating statistics and execution plans, locks, and so on, especially the call operations that retrieve data to the underlying storage engine, which need to be caused by memory operations, CPU operation, and insufficient memory i/ o The operation consumes time.In every query case that consumes a lot of time, we can see that unnecessary extra actions have been written, that some operations have been repeated many times, and that some o

' ===================myisam======================= '. "\ r \ n";$start = Microtime (true);mysql_query ("Select Sql_no_cache sql_calc_found_rows * from foo2 WHERE b = 1 LIMIT 1000, 10");$end = Microtime (true);Echo $end-$start. "\ r \ n";return Result: One query will be so much different!! InnoDB and MyISAM, quickly analyze and analyze why. The first is to use explain to view Determine that both sides are not using index, the second

Tags: retrieving subquery nod type ORDER BY nbsp Best select cannot1.SQL Front plus EXPLAIN to SQL level The meaning of each property Id Serial number of the select query Select_type The type of select query is mainly the difference between common queries and complex queries such as federated queries and subqueries. Table The table that is referenced by the output row. Type The type used by the union

Mysql directly uses limit for paging operations. If we do not add any processing, the data may be too big, and it will be very difficult, next I will introduce the mysql paging instance and paging performance optimization. 1. The most common paging Method for MYSQL: The Code is as follows: Copy code

can avoid Key lookup is perfect, let's revise non-clustered index to include it in the index with the Include keyword His fields are:[SQL] DROP INDEX idx_non_clust_salesorddetaildemo_modifieddate on salesorddetaildemo GO CREATE nonclustered INDEX idx_non_clust_salesorddetaildemo_modifieddate on salesorddetaildemo (ModifiedDate) INCLUDE ( ProductID, UnitPrice ) GO --Clear the cache, only for the development environment! DBCC Freeproccache DBCC dropcleanbuffers GO

Label:MySQL paging uses limit directly to operate, limit if we directly without any processing may be large data will be very card. one of the most common MySQL most basic paging methods: Select * from content ORDER BY id desc limit 0, In the case of small amounts of data, such SQL is sufficient, and the only problem to be aware of is to ensure that the index is used. As the amount of data increases, the number of pages is increasing, and looking

Access the database through ibaties and return the list to Javalist. the mysql console queries the Java code for 0.004 seconds for about 1 minute. In some cases, you can use the mysql management tool to query millions of data. in simple SQL statements, the actual data time is 4 minutes, and the query time returned by t

======================= = '. "\ r \ n ";$ Start = microtime (true );Mysql_query ("SELECT SQL _NO_CACHE SQL _CALC_FOUND_ROWS * FROM foo2 WHERE B = 1 LIMIT 1000, 10 ");$ End = microtime (true );Echo $ end-$ start. "\ r \ n ";Returned results: There will be so many differences in one query !! InnoDB and MyISAM, and quickly analyze why. The first step is to use explain for viewing. No index is used on both sides. The second

One, the most common MySQL most basic way to paging: The code is as follows Copy Code SELECT * from content ORDER BY id desc limit 0, 10 In the case of small and medium data, such SQL is sufficient, the only problem to be aware of is to ensure that the index is used. As the amount of data increases, the number of pages becomes more numerous, and the next few pages of SQL may look similar: The code is

| +---------------------+-------+ | Slow_launch_threads | 0 | | slow_queries | 4148 | +---------------------+-------+ The configuration of the record slow query, the execution time of more than 2 seconds is slow query, the system shows that there are 4,148 slow query, you can analyze the slow query log, find the

MySQL performance query and Configuration Overview View MySQL database performance:Show proceelist view the database connection status and total number of connectionsShow globalstatus like 'max _ used_connections '; view the maximum response connection of the database. The maximum number of connections to

slow query, the execution time of more than 2 seconds is slow query, the system shows that there are 4,148 slow query, you can analyze the slow query log, find the problem of SQL statements, slow query time should not be set too long, otherwise the meaning is not very good,

' ===================myisam======================= '. "\ r \ n";$start = Microtime (true);mysql_query ("Select Sql_no_cache sql_calc_found_rows * from foo2 WHERE b = 1 LIMIT 1000, 10");$end = Microtime (true);Echo $end-$start. "\ r \ n";return Result: One query will be so much different!! InnoDB and MyISAM, quickly analyze and analyze why. The first is to use explain to view Determine that both sides are not using index, the second

Test conclusion 1. Compared to MySQL, theMongoDB database is more suitable for those task models that read the job more heavily . MongoDB can take full advantage of the machine's memory resources. MongoDB's query efficiency can be much faster if the machine's memory resources are plentiful. 2. When inserting data with "_id", the efficiency of MongoDB insertion is not high. If you want to t

EXPLAIN your SELECT query The EXPLAIN keyword helps you know how MySQL processes your SQL statements. This helps you analyze the performance bottleneck of your query statement or table structure.The EXPLAIN query results also show you how your index primary key is used and