speed of updating table

[Anon]

I have one table containing more than 20 million email list. I can update the email list successfully if removal input email address and submit. But it takes too long to process. I think that most of removers will be angry by the speed.

When I add "index" to the row called "email", updating is not working at all.

Any suggestions and input will be appreciated.

[blue928]

this is more a database problem than it is a php issue. It's time for a cluster 🙂. if you have more than one computer available, setup an array so that each computer handles a certain amount of data (ie alphabetical: comp 1 handles a - m, comp 2 holds n - z) doing that you've just cut your search/update time in half. if multiple computers isn't an option, then you'll need to invest in a pretty fast SCSI harddrive (10,000 rpms+) they aren't expensive, but they're damn fast.

blue

[Anon]

The huge table located in Linux platform with mysql and PHP. My server should install the kind of super fast SCSI harddrive?

[blue928]

... correction.. they are expensive, but well worth it..

[Anon]

Or at least, make sure your data files are on a seperate physical drive and using a different drive controller than the web server files. Where data files are in relation to the processes that access those files can have a huge impact on database performance...

Unfortunately, this is usually beyond the realm of control for most folks who write their PHP-MySQL apps and then run 'em on remote machines. 20 million records is a pretty large database. The cluster is a good idea if you have the resources, and en even better idea if the database is still growing.

[Anon]

If I are issued one database, how could I put data in different drive?

Do you think that will speed up performance of updating if I split more than 50 million list into many much smaller tables? Or overall my database won't perform well if I have the huge database?

Thanks for sugestions