CODEWITHSUNDEEP
phpmysqldatabasefile
menardmam
menardmam

Reputation: 9986

PHP read file vs MySql query

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

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

Upvotes: 0

Views: 3077

Answers (1)

Daniel W.
Daniel W.

Reputation: 32280

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.

Upvotes: 3

Related Questions