Insert/ Select help required

[chrisdav]

I have spent a long time trying to get this one - and I am now questioning my data structure

 

I have the following tables at present (telephone billing system)

 

TABLE -DATA

ID

ACCOUNT NUMBER

CALL TYPE

COUNTRY

CALL LENGTH

SEVERAL OTHER FIELDS NOT RELEVANT

 

TABLE - CUSTOMERS

ACCOUNT NUMBER

RATE TABLE

 

TABLE - RATETABLE

COUNTRY

PEAK

OFFPEAK

WEEKEND

 

On a row by row basis - I wish to update (or insert into a new table) the rate that I wish to charge for a call - taken from the DATA table.

 

e.g.

row 1 DATA contains the following information

ID = 1

ACCOUNT NUMBER = ABC123

CALL TYPE = PEAK

COUNTRY CODE = 44

 

I then get the rate table to be used from customers, and use it to select the correct rate \'where ratetable.country = data.country and ratetable.call type = data.call type\'

 

Have managed to do this in php - takes a very long time tho\' as it is running the script on a WHILE loop and there is approx 500k rows in data.

 

It strikes me there must be a more direct way of doing this - if not I will go back and have a look at my php code.

 

Any thoughts gratefully received - if my data structure (or explanation) could be improved then I will do.

[shivabharat]

To do that

 

You have to give a look on indexes

 

Check your mysql help and get the syntax to create indexes

 

Once you do that that will improve the perfomance.

 

Hope this gives u some Idea

[chrisdav]

Thanks for that - presume you mean that my php will work faster if I create indexes on my fields.

 

I will give that a go and see what happens