Optimize SQL by SUBSTRING or chunk column

Question

What is the best way in optimizing this certain query?

For example I have 10 digit phone number and I wanted to query either of these two.

WHERE columnName start from 123456
WHERE columnName1 = 123 OR columnName2 = 456

.

First: Using SUBSTRING

SELECT *
FROM table
WHERE SUBSTRING(telephone, 1,6)

Data:

  id  |  telephone
   1  |  1234560001 
   2  |  1234560002

Second: By chunking column into pieces

SELECT *
FROM table
WHERE pt1 = 123 AND pt2 = 456

SELECT *
FROM table
WHERE pt1 = 123 OR pt2 = 456

Data:

  id  |   pt1 | pt2 | pt3
   1  |  123 | 456 | 0001 
   2  |  123 | 456 | 0002

Which is better? SUBSTRING or chunking the column?

Gordon Linoff · Accepted Answer · 2016-11-26 15:57:38Z

1

If you want these conditions:

WHERE columnName start from 123456
WHERE columnName1 = 123 OR columnName2 = 456

Then use LIKE or IN. The two conditions are:

WHERE columnName LIKE '123456%'
WHERE columnName1 = 123 OR columnName2 = 456

The first can be optimized using an index on columnName. The second is not going to use an index in its current form (but you could force index usage using UNION/UNION ALL with simpler WHERE clauses.

answered Nov 26, 2016 at 15:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sam San Over a year ago

Does LIKE is much preferred than SUBSTRING?

Gordon Linoff Over a year ago

@JamVille . . . Much preferred. When the pattern is a constant/parameter and does not begin with a wildcard, then it can make use of an index.

Collectives™ on Stack Overflow

Optimize SQL by SUBSTRING or chunk column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related