Best and fastest way to walk through database records


I have a mysql database table with around 50k records. Each record has a number column and a rate column.

The number column contains unique numbers like:

  • 3670
  • 3720
  • 654
  • 36
  • 45225
  • 3
  • 675572

So basically every kind of random numbers with various length, it can be 1 digit or even 8 digits long.

I have one number, like 36703657896, my task is to select the longest and shortest matching numbers (match to the beginning of the number) from the number column and get the rate. From the above mentioned list the longest matching number is 3670 (36703657896) while the shortest is 3 (36703657896).

Anyone can suggest a fast solution? It does not necessarily have to be implemented in mysql, I am open for any other solution like some memcache if it is faster. I am currently working with NodeJS and Lua to access this database but I can use anything in case its faster and have a better reason for it to be used.


You can do this in SQL:

SELECT number FROM yourtable WHERE 
   number = substring('36703657896',1,length(number));

This assumes your "number" column is a text type, if not you'll have to do some type casting.

In node.js with node-memcached:

var str='36703657896', maxMatchLength=4; 
var getkeys=[];
for (var i=0; i 
By : Ben Grimm

This video can help you solving your question :)
By: admin