Large table in MySql with Database Caching

[eluser]webdezzo[/eluser]
Hello everyone,

I am getting ready to start on a project that will involve a rather large table within MySql that will need to be queried to verify if a users entry into a form is a valid entry "code". This table will need to be queried upon form submission. Users will have the ability to input 5 "codes" at a time and each one will need to be verified.

The trick is, that the table that contains all of the "valid codes" will be 2.5 million rows, or 2.5 million acceptable codes.

Now I know this is a pretty heavy strain for a database query, and I am wondering if I could see a big improvement on performance by using CI's db caching to create the serialized text file, or if at the point of having 2.5 million codes it is going to be a hog any way we cut it.

Any suggestions on working with this many rows within CI? I am doing my homework before I start getting heavy into that side of the development for the application.

Thanks in advance!

[eluser]Colin Williams[/eluser]
If your MySQL server and file system server are one in the same, I'm not sure how much benefit file system caching is going to provide. Is it going to be that much of a hog though? Have you tried it out?

But, at some point, you just have to throw more hardware at a problem like this. That or rethink your system..

[eluser]Randy Casburn[/eluser]
If you need test data -- GenerateData.com.

Randy