Poor Optimization of nested queriesAs mentioned above, join table queries are more effective than nested queries without special considerations. Although the two queries share the same meaning, even though your plan is to tell the server what to do and then let it decide how to do it, sometimes you have to tell it how to change it. Otherwise, the Optimizer may be stupid. I have encountered such a situation recently. These tables have three levels of h
Basic query optimization and subquery Optimization in Oracle databases
1. Reasonably sort query Conditions
Oracle uses the bottom-up sequence to parse WHERE data. From the perspective of performance optimization, we recommend that you write the conditions that can filter out
Single-Table query optimization: (about the index, after the opening of a single chapter explained)(0) You can use the EXPLAIN keyword first to let you know how MySQL handles your SQL statements. This can help us to analyze the performance bottleneck of the query statement or the table structure.(1) Write SQL to specify the required fields, how many fields to wri
When you submit a query, MySQL will analyze it to see if some optimization can be done to make it faster to process the query. This section describes how the query optimizer works. If you want to know the optimization methods used by MySQL, you can refer to the MySQL referen
Oracle query optimization and oracle Optimization
1. Reasonably sort query Conditions
Oracle uses the bottom-up sequence to parse WHERE data. From the perspective of performance optimization, we recommend that you write the conditions that can filter out a large number of re
The MySQL user can perform. The system administrator who can control the MySQL server or computer can perform additional optimization measures. For example, some server parameters are attached to the query processing process and can be adjusted, and some hardware configuration factors have a direct impact on the query processing speed. In many cases, these optimi
Tags: lexical mass data complete system Check database system an art grammarQuery processing stepsQuery analysis Scanning, lexical analysis, and parsing of query statements. Query check Semantic checking, security and preliminary integrity checking of legitimate query statements Query
This article describes how to analyze MySQL slow query logs by setting parameters to analyze the causes of performance problems, for more information, see query. Currently, most databases provide performance analysis tools. For example, Oracle will help you find slow statements and provide optimization solutions. In MySQL, you need to enable slow log records for
MySQL Query optimization lecture administrator's optimization measures the optimization measures described in the previous section are not executed by privileged MySQL users. The system administrator of the MySQL server or computer can perform additional optimization measure
Table_rows query optimization and table_rows Optimization
In daily application O M, Dev or db must calculate the number of rows in the table to serve as an information reference for the application or maintenance. Many may ignore the impact of SQL statements similar to select count (*) from table_name on database performance, but you can see that the execution h
Let's first review the basic knowledge of field selectivity.
-- Basic knowledge of field selectivity --
Introduction: Can I create indexes for all fields?
As shown in the following table, the sort field has very poor selectivity. You can run the show index from ads command to see that sort's Cardinality (hash level) is only 9, and this field should not be indexed:
Table
Non_unique
Key_name
Seq_in_index
Column_name
Collation
Cardinality
Sub_part
Packed
Null
in my last Post, " " Java " Itoo the actual project Hibernate Lazy Load Optimization Performance "I have mentioned that student data has 2 million, query data is very slow, this is a very difficult thing to see the page progress bar has been rotating circles, the feeling of anxious really can't describe. The first consideration was to use lazy to optimize, because the foreground frame reason,lazy
basic operations for queries 1. The selection action corresponds to a constraint, and the Action object is a row of a two-dimensional table. Optimization mode: Select the operation of the push purpose: Minimize the number of yuan rental before the connection operation, so that the intermediate temporary relationship as small as possible (less than the number of tuples, the number of tuples to be connected) benefits: This may reduce IO and CPU consump
First of all, let's look at one of the steps here that translates into banner, which requires pivot, step-by-step Then look at the overall query results for the two query methods after the main table join So take a look at the latter query method is also through the industry after the conversion to do the join execution plan, you can see only the Word table
This article mainly describes the MySQL query optimizer In the MySQL query optimization series. When you submit a query, the MySQL database will analyze it, it mainly depends on whether it can be used for optimization to make processing of the
Query optimizer of MySQL Query optimization series (1) when you submit a query, MySQL will analyze it to see if some optimization can be done to make it faster. This section describes how the query optimizer works. If you want to
Java Engineering Optimization-ORM framework Query Optimization Principle
As we all know, in the current popular enterprise-level architecture, ORM has always been the most basic part. At the bottom of the architecture design, it provides object-oriented Operation Support for the logic layer, the fact is always different from what we expected. When ORM provides a
ORACLE multi-Table query optimization and ORACLE Optimization
ORACLE has a high-speed buffer concept. This high-speed buffer is used to store the executed SQL statements. Therefore, oracle has to do a lot of work when executing SQL statements, such as parsing SQL statements and estimating index utilization, bind variables, read data blocks, and so on. Assuming th
WHERE rental_date=‘2006-02-14 15:16:03‘ AND customer_id >= 300 AND customer_id 400; Type=ref, indicating that the index was used. 7. Column name is index, COLUMN_NAME is null, using index SELECT * FROM payment WHERE rental_id IS NULL; typical scenario where an index exists but cannot use an index 1. A like query that starts with% SELECT * FROM actor WHERE last_name LIKE ‘%NI%‘; #优化
SELECT * FROM (SELECT actor_id FROM actor WHERE last_name LIKE ‘%NI
null value, and then query: Select ID from t where num = 0 (3) In and not in also to use caution, otherwise it will result in a full table scan, such as: For consecutive values, you can use between instead of in: A lot of times it's a good choice to replace in with exists: (4) Try to use a numeric field, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of
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.