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.
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.
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...
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.