reading from MySQL is faster or reading from a file is faster?

Question

HI

I got a doubt I have seen reading mysql data is slower in case of large tables...I have done lots of optimization but cant get through..

what I am thinking is will it give a better speed if I store data in a piece of file??

off course each data will be a separate file. so millions of data = millions of file. I agree it will consume the disk space... but what about reading process?? is it faster??

I am using PHP to read file...

Answer 1

Reading one file = fast.

Reading many / big files = slow.

Reading singular small entries from database = waste of I/O.

Combining many entries within the database = faster than file accesses.

Answer 2

As long as your tables are properly indexed and as long as you are using those indices (that's right), using a relational DB (like mysql) is going to be much faster, more robust, flexible (insert many buzzwords here), etc.

To examine why your queries' performance does not match your expectations, you can use the explain clause with your selects (http://dev.mysql.com/doc/refman/5.1/en/explain.html).