arrays vs mysql query

a general rule of thumb question

suppose i have a big mysql table and i want to do an operation on each line .. for example, the table contains filenames and i want to delete all the files from my hard drive which don't have their names listed in that mysql table ..

so .. there are 2 ways

1)
// open the folder , take $dirhandle
// for each file in the folder do the following
// mysql_query("SELECT `filename` from `mytable` where `filename` LIKE %$filename%)
// assign the query result to a $handle variable
// if($handle) { delete the file $handle}

and the second way is ..

2)
// $myarray = array()
// mysql_query("SELECT `filename` from `mytable`")
// put all the values of the above query to into $myarray
// for each file in folder, check if the name is in $myarray and delete the file if it's not listed in $myarray


so the main difference between the two is that first does thousands of queries to mysql with each query returning only 1 value but then it doesnt use a memory for building an array, and the second does only 1 query and captures all the results and puts them into the array and leaves the mysql alone after that but eats up memory for keeping and manipulating with the array

in other words, it comes to question like "which is better, to torture the mysql or to torture the php's array logic" ?

Thanks in advance for answers

 

How big is the table? If you want to store whole table in the array.
In a shared hosting environment, I would put less stress on mysql database and go with approach two. I would also store the array in an application / global variable, so you do not query many times (ofcourse based on application's need it may not be feasible)...But if the table is too big to store info in the table, I would go with query.

 

the table is like 30k lines .. so 30k filenames

 

You should do number 2, but iterate using while mysql_fetch_row or similar, because MySQL will marshall the data appropriately. The first only makes a little sense if the process in between rows is meant to run for a long time, and the entire script is low and slow.

 

Always go for memory over disk...

If need be up the memory usage for the PHP process so it doesnt exceed maximum memory size. Depending on how much load os on the db server already that sort of load could bring it to a crashing halt.