What's quicker; including another file or quer

Question

In PHP, which is quicker; using include('somefile.php') or querying a MySQL database with a simple SELECT query to get the same information?

For example, say you had a JavaScript autocomplete search field which needed 3,000 terms to match against. Is it quicker to read those terms in from another file using include or to read them from a MySQL database using a simple SELECT query?

Edit: This is assuming that the database and the file I want to include are on the same local machine as my code.

Answer 1

The difference in time is more down to the system design than the underlying technique I'd dare say. Both a MySQL result and a file can be cached in memory, and the performance difference there would be so small it is neglectable.

Instead I would ask myself what the difference in maintenance would be. Are you likely to ever change the data? If not, just pop it in a plain file. Are you likely to change bits of the content ever so often? If so, a database is way easier to manipulate. Same thing for the structure of the data, if it needs "restructuring", maybe it is more efficient to put it in a database?

So: Do what you feel is most convenient for you and the future maintainer of the code and data. :-)

Answer 2

I recently had this issue. I had some data in mysql that I was querying on every page request. For my data set, it was faster to write a fixed record length file than to use MySQL.

There were a few different factors that made a file faster than MySQL for me:

1. File size was small -- under 100kb of text data
2. I was randomly picking and not searching -- indexes made no difference
3. Connection time -- opening the file and reading it in was faster than connecting to the database when the server load was high. This was especially true since the OS cached the file in memory

Bottom line was that I benchmarked it and compared results. For my workload, the file system was faster. I suspect if my data set ever grows, that will change. I'm going to be keeping an eye on performance and I'm ready to change how it works in the future.

Answer 3

If this is something you're going to be fetching on a regular basis it might be worthwhile to prefetch the data (from disk or the database, doesn't matter) and have your script pull it from a RAM cache like memcached.

Answer 4

definitely include as long as the file isn't too big and you end up using too much memory in which case a database would be recommended

Answer 5

Including a file should almost always be quicker. If your database is on another machine (e.g. in shared hosting) or in a multi-server setup the lookup will have to make an extra hop.

However, in practice the difference is probably not going to matter. If the list is dynamic then storing it in MySQL will make your life easier. Static lists (e.g. countries or states) can be stored in a PHP include. If the list is quite short (a few hundred entries) and often used, you could load it straight into JavaScript and do away with AJAX.

If you are going the MySQL route and are worried about speed then use caching.

$query = $_GET['query'];
$key = 'query' . $query;
if (!$results = apc_fetch($key))
{ 
    $statement = $db->prepare("SELECT name FROM list WHERE name LIKE :query");
    $statement->bindValue(':query', "$query%");
    $statement->execute();
    $results = $statement->fetchAll();
    apc_store($key, $results);
}

echo json_encode($results);

Answer 6

It's very hard/impossible to give an exact answer, as there are too many unknown variables - what if the filesystem is mounted on an NFS that resides on the other side of the world? Or you have the whole MySQL database in memory. The size of the database should be factored in too.

But, on a more answer-y note, a safe guess would be that MySQL is faster, given good indexes, good database structure/normalization and not too fancy/complex queries. I/O operations are always expensive (read: slow), while, as previously mentioned, the whole dataset is already cached in memory by MySQL.

Besides, I imagine you thought of doing further string manipulation with those included files, which makes things even more troublesome - I'm convinced MySQL's string searching algorithms are much better optimized than what you could come up with in PHP.