address.(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.This section is a few trivial suggestions and points for attention when it c
faster.(2) Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query
block according to the pointer on the index block, so that the IO traffic is much less.
Second, index optimization technology
Is there an index that must be retrieved quickly? The answer is yes. Sometimes it's better to use indexes than to index faster. For example, we want to retrieve all the records in the above table, if not indexed, need access to 8,000 x1000 bytes/8k = 1000 pages, if you use the index, first retrieves the index, accesses 8,000 x
Label: index optimization, query optimization, query caching, Server Setup optimization, operating system and hardware optimization, application-level optimization (Web server, caching)
SQL Server query performance optimization analysis transactions and locks (2 ). Step 1: Execute Example 2 and Example 3 code in the query analyzer. (This is the example in the previous article SQLSERVER query performance optimization analysis transactions and locks (2) -- Ex
% ' InStr (title, ' manual ') =0 equivalent to title not a like '% manual% ' Fuzzy query optimization: Knowing the use of the InStr function, optimization becomes simpler, such as the% keyword% equivalent to InStr (field, ' keywords ') >0 Practical application: there are nearly 11 million data in the T table, and many times we want to do string matching, in SQ
MySQL limit query optimization bitsCN.com
MySQL limit query optimization the following articles mainly introduce the specific content of MySQL limit query optimization. we all know that MySQL database
MySQL query optimization: profile function. Note that you need to install the profile module.
MySQL query optimization: profile function. Note that you need to install the profile module.
MySQL query optimization: profile fun
order in which tables are read is forcibly specified by left join, and the use of STRAIGHT_JOIN can help the connection optimization program to execute faster, because there will be fewer table queuing checks. Note: If you execute the following type of query, MySQL performs a full table scan on B, because LEFT JOIN forces that you must do this before reading d:
SELECT *
FROM a,b LEFT JOIN c ON (c
explanation:
Qcache_free_memory
The amount of space currently remaining in the query cache.
Qcache_hits
The number of hits for the query cache.
Qcache_inserts
Number of query cache insertions
Qcache_free_blocks
How many remaining blocks are there at the moment? FLUSH
bytes;2. Data item: Whether there is a large number of paragraphs, the values of those fields are often updated;3. Data query SQL Condition: The column names of which data items often appear in the Where, GROUP by, and ORDER by clauses are medium;4. Data Update class SQL Condition: How many columns often appear in the WHERE clause of update or delete;5.SQL amount of statistical ratio, such as: select:update+delete:insert= how much?6. What is the magn
unfamiliar, specific usage and field meaning can refer to official website Explain-output, here need to emphasize rows is the core indicator, most of the rows small statement execution must be very fast (with exception, as described below). So the optimization statements are basically optimizing rows.Basic steps for slow query optimization0. Run first to see if it is really slow, note the setting Sql_no_ca
In the optimization query, database applications (such as MySQL) mean the operation and use of the tool. Using indexes, using explain to analyze and query, and adjusting the internal configuration of MySQL can optimize the query.
Any database programmer has the following experience: a bad SQL
Tag: SEL Access SQL statement through Date diff Union All database table variableIn a TENS database search, how to improve the efficiency of the query?1 ) Database design aspects: A, to optimize the query, should try to avoid full table scan, first of all should consider the where and order by the columns involved in the index.b, you should try to avoid the null value of the field in the Where clause to jud
resources because it looks for duplicate records, even if you're sure they don't exist.So if you know that there are no duplicates in the recordset you want to federate, then you want to use union all instead of union. The Union all Union recordset, but does not search for duplicate records, thus reducing the use of SQL Server resources to improve performance.3. Try not to select *:In most cases, do not use * to replace the field list returned by the query
in the flow log table. it is estimated that it may reach 40 million records per day. using a table per day is also very large. I have read it. the MYD file size is more than 8 GB.
When we query the log records in the management system, we need to perform conditional queries on several fields, and several fields have a length of 256. in the 8G table, we need to query a string. if the string cannot be found,
largest number of records in the flow log table. it is estimated that it may reach 40 million records per day. using a table per day is also very large. I have read it. the MYD file size is more than 8 GB.
When we query the log records in the management system, we need to perform conditional queries on several fields, and several fields have a length of 256. in the 8G table, we need to query a string. if t
When querying database records using SQL statements, there are many different ways to query the same content.Still, although you can get the same results using a variety of methods, if you use a different approach, the execution benefits are quite different. Therefore, we have to consider carefully, if you want to query the same results, which statement to use, the implementation of the benefit is better.Th
database application development tools and supports Java™SQL pl and PL/SQL routines, XML editor, and other development methods. It can also be integrated with IBM's query optimization tools to Optimize Query performance.
Monitoring and Management of database health and availability plan jobs. Data Studio 3.1.1 provides a Web-based monitoring tool for health an
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.