database problem

need to handle a huge database..without causing a load on the server

[indiandomain]

guys i need some help in managing a huge database.
the database has around 500,000 keywords and related terms for the main keyword.

the format of the databse is like

loans:home_loans,business_loan,free_loan,car_loan
health:womens_health,men_health,dog_health
.
.
.
.

now when someone searches for a term like 'loans' on my site,my server should querry the databse and show the results 'home_loans,business_loan,free_loan,car_loan' on my website.

i initially tried using a .txt database but the server couldnt handle the load as the querries ran into thousands per day.

can someone tell me how to manage the database without causing too much load on the server.
btw i have a linux box.

i am planning to use mysql but my programmer says even mysql would cause a load since the database is huge.

please advice.

thanks
-Sohail

[lorax]

Personally I don't think that 500K records is huge. It's probably average as far as commercial grade data is concerned.

I also don't think there's a straight forward answer though hopefully someone more knowledgeable than I will correct me if I am wrong.

I'd suggest you do some benchtesting to determine performance of your particular situation. There are at least two options for allowing searchs by your users. Live queries and cached queries/results. Which one will work best for you depends upon your particular situation.

Another thought - some of the performance issues can be reduced by good database normalization. But in any case, 500K records will take a moment to read and do something with.