What's quicker; including another file or querying a MySQL database in PHP?

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

I exactly don't know, but in my opinio using MySQL, even if can be slower, sould be used if the content is dynamic. But I'm pretty sure it is faster, for big contents, using include.

Answer 2

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 3

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.

Answer 4

Reading in raw data to a script from a file will generally be faster than from a database.

However it sounds like you are wanting to query that data in order to find a match to return to the javascript. You may find in that case that MySQL will be faster for the actual querying/searching of the data (especially if correctly indexed etc.) as this is something a database is good at.

Reading in a big file is also less scalable as you will be using lots of server memory while the script executes.

Answer 5

As wolfie said, it depends. If your file is stored locally in your server and the mysql is installed in another machine, then the faster is to include the file.

Buuuuut, because it depends on your system it could be not true. I suggest to you to make a php test script and run it 100 times from the command line, and repeat the test througth HTTP (using curl)

Example:

use_include.php

<?php

start = microtime(true);

include( 'somefile.php' );

echo microtime(true)-start; ?>

use_myphp.php

<?php

start = microtime(true);

put_here_your_mysql_statements_to_retrieve_the_file

echo microtime(true)-start;

?>

Answer 6

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 7

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 8

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 9

Why not do it both ways and see which is faster? Both solutions are pretty trivial.

Answer 10

If you expect the number of terms to become larger at a later date, you're better off using MySQL with a fulltext search field.

Answer 11

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 12

If you use a PHP bytecode cache like APC or Xcache, including the file is likely to be faster. If you're using PHP and you want performance, a bytecode cache is absolutely a requirement.

It sounds like you're considering keeping static data around in a PHP script that you include, to avoid hitting the database. You're basically doing a rudimentary cache. This can work okay, as long as you have some way to refresh that file if/when the data does change. You might also look want to learn about the MySQL Query Cache to make SQL queries against static data faster. Or Memcached for keeping static data in memory.