In this talk about the sqlscript on the internet spread the misunderstanding, in order to avoid baseless assertion, not the real DBA Expert, if their technical speech is not clear to the no, can verify the best to write code to test their own to know. The following my views are also welcome to correct, do not blindly believe others, sometimes with suspicion of the eye to learn the truth may be explored.
1. Some people say that exist faster than in, the actual is not necessarily the case, if it is true I think the database vendor will not need in.
2. "In and not" should also be used with caution, otherwise it will result in a full table scan, which is not correct.
3. You should try to avoid being null in the WHERE clause, otherwise it will cause the engine to abandon using the index for a full table scan, in fact, this is not the case.
4. Avoid inverting on the table, otherwise the index on the corresponding field will not be used. The tested,<> and! = Operation symbol can also be used with index.
5.SELECT COUNT (1) From TableName This statement is faster than SELECT (1) from TableName, and is also incorrect.
6. The statement of the order of the connection table or the order of the condition affects the execution speed of the sentence, even before the forum friends said he heard people say that many tables connected to the small table need to write in front and back, after the table written in the back, so that will be fast. or the order of the field in the Where condition needs to be consistent with the order in index. These remarks are not supported by theory and are not correct.
7. When an index is indexed on a table, the index is bound to take effect if the indexed column is used at query time.
Remark:it essay, if reproduced please indicate out, thank you!
Terryxia
The misunderstanding of SQL code writing