Effective LIKE database search '% something%'

Question

I am trying to search by phone numbers for any phone number containing multiple digits.

Obviously, the following will be slow:

 Select * from customer where phone like '%1234%'

I need wildcards because users are allowed to enter any data in the database, so it can have country codes leading 1 (like in 1-800) or back extensions (which sometimes just share space.

Note. I already created “cleared” phone numbers by deleting all characters without numbers, so I don’t have to worry about dashes, spaces, etc.

Is there any magic to finding this type of search in a reasonable amount of time?

Answer 1

MySQL, http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

, , , . MySQL, MyISAM ( InnoDB .)

, .

Answer 2

.

, . , , , .

, : 2125551212 :

1
2
5
12
21
25
51
55
121
125
212
255
512
551
555
1255
2125
2555
5121
5512
5551
12555
21255
25551
55121
55512
125551
212555
255512
555121
1255512
2125551
2555121
12555121
21255512
212555121
2125551212

, :

create table myindex (
    key varchar(10) not null,
    datarowid integer not null references datarows(id)
);
create index i1myindex(key);
insert into myindex values('1255', datarow.id);

, .

, 4 , , 4 .

, , "% 123456%", "1234", .

select d.* from datarows d, myindex i where i.datarowid = d.id and i.key = '1234' and d.phone like "%123456%";

, db .

, , , .