Hi all,

I’ve an interesting problem and could use some suggestions for improving the performance…

I have a MyISAM table containing about 650,000 text prefixes. These prefixes are telephone numbers such as international dialling codes, local exchange codes and also complete phone numbers.

create table numbers

{

number_id bigint unsigned auto_increment,

number char(32),

unique key (number_id),

key ( number, number_id)

}

There are some more columns of meta-info in the actual implementation but they are not important…

I am searching this table to find all matches to a complete telephone number, for example the search will return any matching prefixes and the complete number if present:

select number_id from numbers where number = left(“12345678”, length(number))

This is taking too long, in the order of seconds (on a fairly underpowered 1 CPU server to be honest) but is using the index according to EXPLAIN.

I have tried adding an extra column containing the length of the number and using that instead of the length(number) calculation in the query which helped allot, but now its not using the index.

create table numbers

{

number_id bigint unsigned auto_increment,

number char(32),

number_length int unsigned,

unique key (number_id),

key ( number, number_id)

}

select number_id from numbers where number = left(“12345678”, number_length)

Any suggestions for speeding up this kind of wierd reverse-prefix lookup greatfuly received )

Cheers!

Rupert