CODEWITHSUNDEEP
mysqlquery-optimization
Sam San
Sam San

Reputation: 6903

Optimize SQL by SUBSTRING or chunk column

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.

  1. WHERE columnName start from 123456
  2. 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?

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you want these conditions:

  1. WHERE columnName start from 123456
  2. 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.

Upvotes: 1

Related Questions