We are building a website which uses a some static data like "countries, cities related to each country, mountains and so on".
My friends suggest that its faster to store them in a CSV file and import them using javascript. He says its faster than gettings them from mysql.
Me on the want to stick with mysql so that we have everything in one place. I dont think that 0.00001 sec really matters.
Can you help us? When should we use mysql and when csv?
Faster might not be the best criterion for deciding what to do.
I'll bet your friend has no data to support or refute either recommendation.
Isn't a JavaScript import a network hop, just like a call to the database?
I'd compromise: put them in MySQL, load them into memory on startup, and cache them in the middle tier. You'll only pay for the network hop to GET them from the middle tier to the browser and the data will be in a real database.
If you don't have a middle tier, then you're out of luck.
Does every user need all the values in that table, or just a few? Why force every user to accept a big download?
In general, predictable files in predictable locations are much faster than DB Lookups. DB is Find/Locate, pull from Disk, Deliver. File is where you want it to be and no look ups involved.
Many caching techniques will store content to a DB for administrative purposes, but the content of the entry is also written to file for quick lookup when needed.
You may want to consider a hybrid of both. DB management, and file caching. You'll get the best of both worlds, and build a platform for later use beyond State list management.
There are a number of caching techniques, and approaches. For example, I deploy on demand caching. If the file is not located where I expect, I cache from the DB into a file dynamically written on demand. When the DB Entry is updated, kill the cached file. Subsequently the file is written when it is requested on demand.
For static data of that kind the only real incentive for using MySQL (that I can think of) is for database validation. I'd suggest just going with the CSV, particularly if you're not planning to change it.
Retrieving data from a CSV may be faster, but it doesn't give you referential integrity. If you're saying something like
users (
country_id,
locale_id, ...
)
If you want you can cache that data in a CSV, but duplicate it in the DB so you can have some referential integrity. That way when you query the data all of the fields with info about that country or whatever will come back with whatever record is meant to join it. That way you get the benefit of caching it and retrieving it from a faster data store without losing referential integrity.
Using MySQL to store countries is a good idea in my opinion because this let you ensure data consistency through foreign keys.
Speed is not very important in such cases because you deal with few data (several hundreds rows).
Keep in mind that storing such data outside of the database will probably increase significantly the complexity of your application.
Hope this helps ;-)
Maybe use SQLite ? Its got some benefits of both CSV and MySQL