Hello, I have a high traffic site will be generating a page that mashes up around 70 different resources per page (70 mysql queries). I'm thinking of saving the 70 elements as a flat file, so instead of 70 queries everytime a page loads, it does 1 query for the filename, and loads one flat file that's about 30k each (in this case, doing a join or combining the queries is not possible and not an option). I will have about 8,000 files, one for each page, and I may put them all in one folder on the server. The files will be read only and won't change. Many people online have said how mysql is so much faster than flat files. But for this case, my mysql database is already 150MB in size, and would you suppose loading one 30k file might be faster and less burdensome on the server than 70 queries on a 150MB database?
Another option is make the 8,000 pages of content and store the html in mysql, but it would add 240MBs to the already large 150MB database. And all the data is redundant with that's already in mysql, so I'd feel bad (it just takes 70 queries to put it together, which probably isn't a good idea). But flat files? Anyone have experience with this?
I doubt it would be faster but it really depends on the exact situation. Disk lookups are around the slowest operation that a script will do. Looking up a text file, then parsing it, then performing some operation based on what it contains, it unlikely to be faster than the database, provided the database is properly tuned for what you are doing. Make sure the db is optimized towards you usage, query optimization as well as table and database structure, and parameter optimization. I would also look into query and front end caching to prevent redundant queries from hitting the database. If these are generating html pages, you could also create a caching mechanism that caches the entire html page and then periodically checks for an indicator, or length of time, that the page needs to be regenerated. Only in the case of actual static files or image storage would I normally recommend using a file based route instead of a database.
Thanks for the input. These are just static files of html content and would be loaded like a php include, not processed. So, it's whether the 8,000 pages @ 30k each should be flat files or as a 240MB mysql database. Anyone care to vote on which may be faster?
There two main ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) orLOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can be optimized better and a bit faster because of easier parsing.
If you have very big dataset you can use this constrcutions: mysql> SET SESSION BULK_INSERT_BUFFER_SIZE=256217728; mysql> set session MYISAM_SORT_BUFFER_SIZE=256217728; mysql> set global KEY_BUFFER_SIZE=256217728; mysql> alter table load1 disable keys; mysql> LOAD DATA INFILE '/home/user/tmp/your_file.csv' IGNORE INTO TABLE load1 FIELDS TERMINATED BY ','; mysql> alter table load1 enable keys;
If they're going to be a static html file, you should go with that way. Loading 1 static file is more efficient than doing 70 queries each time a page is loaded. You're going to need to do the 70 queries, 8000 times to get these static files though.