Avoid using like in SQL statements whenever possible

Source: Internet
Author: User

---- Start

We have introducedAvoid using functions in the WHERE clause of SQL statements.This will invalidate the index on this field and affect the performance of SQL statements. Based on the same principle, we should also avoid usingLike. Consider the following:

Create Table user <br/> (<br/> name varchar (20) not null, --- name <br/> mynumber varchar (18) --- ID card number <br/> ); 

What should I do if I ask you to check the ID card number starting with 2102 (from Dalian? We naturally write this:

Select * from user where mynumber like '123 '; 

The preceding statements are completely correct, but the performance is poor. How can this problem be solved? The answer is to convert it into a range scan, as shown below:

Select * from user where mynumber> = '000000' and mynumber <'000000 '; 

--- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2009.9.24

---- Written by shangbo on 2009.9.24

---- End


Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.