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.

6 Years
Discussion Span
Last Post by debasisdas

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.

Votes + Comments
almost there

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...

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.