I am in the middle of a project where i need to be able to get a value (phone number) from one table then compare it to another set of numbers (phone number rates) that are in another table and i was wondering what the most efficient way to do this would be?
I would be using PHP and then calling SQL from that but which is most efficient way to search a table to compare
for example would it be quicker/slower if my rates table was from smallest to largest or visa versa?
0 00 000
or
000 00 0
And how would i code this to make it as quick as possible as i am comparing around 60k lines per time i run this script
Please see below for example of the two tables:
Table 1: Phone Numbers
Date, CLID, DNID, Duration
10/3/14, 01344883333, 01344883321, 60
10/3/14, 01344883333, 07839451009, 60
10/3/14, 01344883333, 07767045933, 60
Table 2: Rates
prefix, description, cost
44, UK - Local, 0.005
07760, UK - Mobile - Vodafone, 0.05
07760,UK - Mobile - Vodafone,0.05
07765,UK - Mobile - Vodafone,0.05
07766,UK - Mobile - Vodafone,0.05
077670,UK - Mobile - Vodafone,0.05
077671,UK - Mobile - Vodafone,0.05
077672,UK - Mobile - Vodafone,0.05
077673,UK - Mobile - Vodafone,0.05
077674,UK - Mobile - Vodafone,0.05
077675,UK - Mobile - Vodafone,0.05
077676,UK - Mobile - Vodafone,0.05
077677,UK - Mobile - Vodafone,0.05
077678,UK - Mobile - Vodafone,0.05
077679,UK - Mobile - Vodafone,0.05
07768,UK - Mobile - Vodafone,0.05
You could get this down from 60K lines per search (I assume that's the number of rows in your Rates table), down to the max length - min length of any prefix by running one query at a time until you got your answer like this:
If the max length of any prefix is 6 characters, and you're searching for the rate for the phone number '07767045933', then search for the rate like this:
SELECT *
FROM Rates r
WHERE r.prefix = LEFT('07767045933', 6)
If no result, then search for:
SELECT *
FROM Rates r
WHERE r.prefix = LEFT('07767045933', 5)
etc., down to your minimum prefix size.
Equality searches like this can utilize an index. Ensure that there is an index on Rates(prefix).
Or course, you could extract the substring from the phone number in PHP instead of SQL. Using a prepared statement, there would be little overhead in switching out the string until you found the rate.