CODEWITHSUNDEEP
mysqlphone-number
beobeo88
beobeo88

Reputation: 165

Fast search solution for numeric type of large mysql table?

I have large mysql database (5 million rows) and data is phone number. I tried many solution but it's still slow. Now, I'm using INT type and LIKE sql query for store and searching phone number. Ex: SELECT phonenumber FROM tbl_phone WHERE phonenumber LIKE '%4567' for searching phone numbers such as 170**4567**, 249**4567**,...

I need a solution which make it run faster. Help me, please!

Upvotes: 2

Views: 1530

Answers (4)

Gerardo Lima
Gerardo Lima

Reputation: 6712

You are storing numbers as INT, but querying then as CHAR (the LIKE operator implicitly converts INTs to CHARs) and it surely is not optimal. If you'd like to keep numbers as INT (probably the best idea in IO performance therms), you'd better change your queries to use numerical comparisons:

-- instead of CHAR operators 
WHERE phone_number LIKE '%4567'
WHERE phone_number LIKE '1234%'
-- use NUMERIC operators
WHERE phone_number % 10000 = 4567
WHERE phone_number >= 12340000 -- considering 8 digit numbers

Besides choosing a homogeneous way to store and query data, you should keep in mind to create the appropriate index CREATE INDEX IDX0 ON table (phone_number);.

Unfortunately, even then your query might not be optimal, because of effects similar to @ron have commented about. In this case you might have to tune your table to break this column into more manageable columns (like national_code, area_code and phone_number). This would allow an index efficient query by area-codes, for example.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108530

That LIKE predicate is operating on a string, so you've got an implicit conversion from INT to VARCHAR happening. And that means an index on the INT column isn't going to help, even for a LIKE predicate that has leading characters. (The predicate is not sargable.)

If you are searching for the last digits of the phone number, the only way (that I know of) to get something like that to be fast would be to add another VARCHAR column, and store the reversed phone number in it, where the order of the digits is backwards.

Create an index on that VARCHAR column, and then to find phone number that end with '4567':

WHERE reverse_phone LIKE '7654%'

-or-

WHERE reverse_phone LIKE CONCAT(REVERSE('4567'),'%')

Upvotes: 0

favoretti
favoretti

Reputation: 30207

I would experiment with using REGEXP, rather than LIKE as in the following example:

SELECT `field` WHERE `field` REGEXP '[0-9]';

Other than that, indeed, create an index if your part of the phone search pattern has a constant length.

Here is also a link to MySQL pattern mathching document.

Upvotes: 0

Luke Baughan
Luke Baughan

Reputation: 4686

Check the advice here How to speed up SELECT .. LIKE queries in MySQL on multiple columns?

Hope it helps!

Upvotes: 0

Related Questions