steps to optimize sql query

Note: Use the BTREE compound to index the ASC/DESC of each field to optimize the orderby query efficiency.The tbl_direct_pos_201506 table has 1.9 million data, DDL: Create table 'tbl _ direct_pos_201506 '('acq _ ins_code' char (13) not null default ''comment' mechanism Code', 'trace _ num' char (6) not null default ''comment' trace NO. ', 'Trans _ datetime' char (10) not null default ''comment' transaction

the SQL execution plan: ID Select_type Table type Possible_keys Key Key_len ref rows Extra 1 Simple T Range PRIMARY PRIMARY 24 299392 Using index condition; Using Filesort 1 Simple R Eq_ref PRIMARY PRIMARY 41 Msp.t.recv_ins_code 1 Using where 1 S

Note: Use BTREE to comindex ASC/DESC of each field to optimize order by query efficiency. btreeascThe tbl_direct_pos_201506 table has 1.9 million data, DDL: Create table 'tbl _ direct_pos_201506 '('acq _ ins_code' char (13) not null default ''comment' mechanism Code', 'trace _ num' char (6) not null default ''comment' trace NO. ', 'Trans _ datetime' char (10) not null default ''comment' transaction time', '

at the SQL Run schedule: ID Select_type Table type Possible_keys Key Key_len ref rows Extra 1 Simple T Range PRIMARY PRIMARY 24 299392 Using index condition; Using Filesort 1 Simple R Eq_ref PRIMARY PRIMARY 41 Msp.t.recv_ins_code 1 Using where 1

Detailed analysis of SQL Execution steps and SQL Execution steps Detailed analysis of SQL Execution steps First, let's look at the sequence of statements executed. (8)select (9)distinct A (1)from Ta (3)join Tb (2)on XXX (4)where X

Let's take a look at how to simplify this mysql statement and optimize the query efficiency ~~~~~ This post was last edited by setoy at 12:03:34 There are three tables: the administrator table, the department table, and the score table for the department as follows: Now you need to query and count the scores of each department. even if a department does not h

the table to reduce the table size (sp_spaceuse)3. upgrade hardware4. Create an index based on the query conditions, optimize the index, optimize the access mode, and limit the data volume of the result set. Note that the fill factor should be appropriate (preferably the default value 0 ). The index should be as small as possible. Use a column with a small numbe

per page, currently, there are 10th pages. The maximum id of the current page is 9527, and the minimum is 9500, if we only provide jumps such as "Previous Page" and "next page" (do not provide jumps to page N), the SQL statement can be:SELECT * FROM message WHERE id> 9527 order by idASC LIMIT 20;When processing the next page, the SQL statement can be:SELECT * FROM message WHERE id No matter how many pages

When I use the "database engine optimization consultant" of sqlserver2008 to optimize an SQL statement today, the following suggestions are provided in the index recommendations:-- Create statisticsCreate statistics [_ dta_stat_1758629308_16_10_1] on [DBO]. [prmorderpromisetime] ([distributionid], [storeid], [orderid]) -- Include IndexCreate nonclustered index [_ dta_index_prmordersiteactualtime_6_1582628

SegmentFault (www. sf. gg) is a leading developer technology community in China. We hope to provide programmers with a pure and high-quality technology exchange platform to learn, communicate and grow with developers and create an era for developers! Reply content: 1 million of the data volume is small, and the optimization is generally to add an index. There is no index; Does the query statement use indexes; What is the distributi

Optimize SQL Server 2000 settings SQL Server has been well configured to optimize its own performance, which is much better than other relational databases on the market today. However, you still have several settings that need to be modified so that your database can process more transactions (TPM) per minute ). Thi

Optimization SQL Server DatabaseThere are many reasons for slow query speed, which are common in the following ways: 1. No index or no index is used (this is the most common problem of slow query and is a defect in programming) 2. Low I/O throughput, resulting in a bottleneck effect. 3. the query is not optimized becau

Briefly, tracking the SQL emitted by one client is mainly divided into the following steps: 1 Identify the client program to be tracked to the database connection (followed by session instead), mainly to find the only one to identify a session SID and Serial#. 2 set the corresponding parameters, such as opening the time switch (you can know how long a SQL executi

in the range 1 to 4000, using the index file first.The next step is to find out the table's records through these IDs, because the IDs are discrete, so MySQL's access to the tables is not sequential read.And this table is very large, can not be loaded into memory at one time, so every access to a record MySQL to re-locate on the disk and the nearby records are loaded into memory, a large number of IO operations caused by the speed of the decline.Problem Solutions:1. Partitioning the table on a