query optimization in dbms

In the previous chapter, we explained how to optimize schema. This is a necessary condition for high performance. But just thinking about schemas is not enough-you also need to design your query. If your query is poor, even if the schema design is perfect, it is useless. Query optimization, index

tablesRow_number test results for pagination:Paging using row_number: CPU time = 317265 milliseconds, elapsed time = 423090 millisecondsPaging using temporary tables: CPU time = 1266 milliseconds, elapsed time = 6705 millisecondsThe Row_number implementation is based on order by, and the effect of sorting on the query is obvious. OtherThe use of indexes is limited by the wordingSelect * FROM TableName where chgdate +7 Select * FROM TableName wh

table, one is plus, One is multiply, and the merge join effect is much better than nested Loop join.If there is no index on the connected field, then the efficiency of the SQL2000 is quite low, and SQL2005 provides a hash join, which is equivalent to a temporary index of the result set of A/b table, so SQL2005 efficiency is much higher than SQL2000, which I think is an important reason.To summarize, pay attention to the following points when connecting tables:(1) Connection field select the fie

tbl_name WHERE key_part_1=constant;mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10;mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10; The following queries can only be solved using the index tree (assuming that the index column is Numeric ): mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;mysql> SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2;mysql> SEL

need to take the value of the time to really do. But you also need to be careful about the side-effects of this mechanism, because this is likely to degrade performance by creating many, many small queries. ORM can also package your SQL statements into a single transaction, which is much faster than executing them alone. Currently, the personal favorite of PHP's ORM is: Doctrine.21. Be careful with "permalink"The purpose of the permanent link is to reduce the number of times the MySQL link is r

1. UseProgramTo minimize the number of accesses to the database. By searching for parameters, You can minimize the number of rows accessed to the table and the result set, thus reducing the network burden; separate operations should be processed separately to improve the response speed each time. When using SQL in the data window, try to place the indexes in the first column selected;AlgorithmThe structure should be as simple as possible; During the query