is it better to query database or grab from file? php & mysql

I am keeping a large amount of words in a database that I want to match up articles to. I was thinking that it would just be better to keep these words in an array and grab that array whenever needed instead of querying the database every time (since the words won't be changing that much).

Is there much performance difference in doing this?

And if I were to do this, how to I write a script that writes the array to a a new 开发者_运维问答php file. I tried writing the array like so:

while( $row = mysql_fetch_assoc($query))
{

 $newArray[] = $row;

}


$fp = fopen('noWordsArr.php', 'w');
fwrite($fp, $newArray);
fclose($fp);

But all I get in the other file is "Array".

So i figured I could write this and then write have a chron hit up the file every few days or so in case things have changed. But I guess if there is no performance advantage then it prob won't be necessary and I can just query the database every time I need to access the words.

You could write an array to the file like this:

fwrite($fp, var_export($newArray, true));

Update Based On Comment:

You could also use the serialize() function there as rightly suggested by Bill Karwin.

For writing data structures to a file, use serialize and unserialize. So:

fwrite($fp, serialize($newArray));

And then later, when you retrieve the file:

$newArray = unserialize(file_get_contents("noWordsArr.php"));

Hope that helps,

Thanks, Joe

The reason that writing to the file as described fails is that fwrite() takes a string as a parameter, so it converts the array into a string 'Array' and writes that.

You need to do the conversion to a string yourself - try:

fwrite($fp, print_r($newArray, true));

If most of your work will just be looking if a given word is in your list, the fastest way is probably storing the list in alphabetical order with an hash index. You will be rebuilding the list at every update in a temporary file and use the newly generated file to atomically update the older one.

Something like DJB's cdb format (also implemented in postfix) or postfix other list formats - they're all file formats optimized for fast readonly lookups and infrequent atomic updates and I'm sure there are many other implementations of the same concepts.

Any SQL layer between the app and the list will just add overhead and will end up doing the same thing (the sql db will be looking up an index to find rows in your word list), it'll be faster only if you use SQL to split the workload between two physical system (your app on one, and the sql server on the other one).

If the list will be small enough to be kept in memory, just load it from whatever format you like and store it in a structure equivalent to the indexed files I was talking about.

On the other hand, the performance difference between these approaches will be negligible unless the list is very big or you do a lot of lookups.

I would recommend looking at some caching solution. For example PEAR Cache_List is very easy to use and would do what you need. Just keep your words in a file cache and refresh it either over time or on demand.