0

Here is the plan. I have a large CSV file extract from a DB with 10 000 entry. Those entry look like :

  • firstname
  • lastname
  • tel
  • fax
  • mobile
  • address
  • jav-2012-selltotal
  • fev-2012-selltotal
  • etc.. etc..

So, i have read about getting those CSV data to MySQL database, and query those database to know who have sold more in feb 2012, or what is the selling total of john.. or whatever i ask to...

But for optimization purpose, caching, optimizing and indexing query is a must... witch lead me to this question. Since i know the 2-3 query i will do ALL THE TIME to the DB... is it faster to take the CSV file, to make the request in PHP and write a result file on disk, so all my call will be readfile-load-it, display-it ?

another wording of the question...is making query to DB is faster or slower that reading file to disk ? because if the DB have 10 000 record, and the result of the selling of paul is 100 line, the file will ONLY contain 100 lines, it will be small... the query will always take about the same time

PLease help, i dont what to code it myself just to discover thing that is evident to you... thanks in advance

Improve this question
1
  • Writing result of the query to FILE will make all the DB optimizing, indexing completely useless talk ? reading the file will always be "perfect" because i know that in 2012-result.txt is the answer i need, and in paul-file.txt all the paul selling Commented Nov 28, 2013 at 15:49

1 Answer 1

Reset to default
3

If you stick to database normalization rules and have everything in database, you are just fine. 10k records is not really much and you should not have to worry about performance.

Database queries are faster because the data gets (partially) cached in the memory rather than on plain disc unless fully read into RAM.

A handful plain text files might be faster at first sight, but when you have 100k files and 100k datasets in the DB, database is so much better,.. you don't have unlimited (parallel) inode access and are slowing and killing your harddrive/ssd. The more files you have, the slower everything gets.

You'd also have to manually code a locking queue for read/write actions which is already integrated into MySQL (row- and table locking).

Consider in a few months you want to extend everything,... how would you implement JOINS in text files? All the aggregation functionality MySQL already has built in (GROUP BY, ORDER BY,...).

MySQL has a profiler (use EXPLAIN before each statement) and can optimize even bigger datasets so much.

When I went to school I said to my teacher: 'Plain files are much faster than your MySQL'. I made a site with a directory for each user and stored attributes in a textfile each inside that user folder, just like: /menardmam/username.txt, /menardmam/password.txt, /DanFromgermany/username.txt, .... I tried to benchmark this and ya text file was faster, but only because it was just 1000 text files. When it comes to real business, 1000000000 datasets, combined and cross joined, there is no way to do this with text files and it is much better when applying for a job to present your work with MySQL than what you done with text files.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

i have read that manually caching in file the result of the query is ALWAYS faster that doing the query... and i said 10 000 for the example, it can be 100 000, million or just 60 !
Your school experiment proove my point... i will not grow database like facebook... i will be keep small, under 100 000 record !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.