I am trying to store millions of records with address, city, state, zip, etc. I originally had one table with 17 different fields with the primary key being id. However, I soon realized after inserting almost a million, the website began to lag and was slow.

What is the best way to speed up the site? The information needs to be able to be read and changed when needed, and most importantly load fast.

Re: PHP/MySQL Storing Millions of Records Issue 80 80

Make sure that you have an index for all search criteria. And combined indexes for combined search criteria. Let MySQL EXPLAIN your query to optimize it. Add more RAM to your server. Increase the key buffer and other cache variables in my.ini.

commented: almost there +8
Re: PHP/MySQL Storing Millions of Records Issue 80 80

1. Need to ensure there is INDEX on search columns
2. Need to disable INDEX during data loading operation, and enable later on.
3. Need to check the SQL, may need to tuned.
4. Need to check for Hardware.
5. Need to check how loaded is your network.
6. May need to use caching from application side.

and few more...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.