php - Multiple DB lookups VS fetch all + array search, what's more efficient

914

I'm sure this has been discussed many times before, but for whatever reason I didn't find anything (could be no coffee).

As a design question here's the idea.

One (remote) database looks something like thisid|timestamp|log_entry|task_id

These are fetched to a PHP/Codeigniter portal and mapped to a local databasetask_id|name|...

Now, parsing through the remote data I need to (among other things) get the name associated with each row. The simple way would be to loop through the result, and in each iteration do a lookup (id -> name). But this will generate a lot of DB calls. The other way I see it is to pre-fetch all id-name pairs into an array, and then use that for lookup.

For this specific project I'm not expecting performance to be an issue either way. But which would be the most efficient way to do this?

EDIT: Pseudo code

<?php
//

441

Answer

Multiple queries example ------ $log_result = mysql_query('SELECT id, task_id, log_entry, timestamp FROM remote_db WHERE date=X'); foreach ($log_result as $log_row) { // Get task name for row $task_name = mysql_query("SELECT name FROM local_db WHERE id={$log_row['task_id']}"); // Process this row ... echo "Proccesed {$task_name} which occured at {$log_row['timestamp']}"; } //

770

Answer

Array example

455

Answer

------- $task_lookup = mysql_query('SELECT id, name FROM local_db'); $log_result = mysql_query('SELECT id, task_id, log_entry, timestamp FROM remote_db WHERE date=X'); foreach ($log_result as $log_row) { // Get task name for row // assume task_lookup[] = array(id => name) $task_name = $task_lookup[$log_row['task_id']]; // Process this row ... echo "Proccesed {$task_name} which occured at {$log_row['timestamp']}"; } ?>

245

Answer

Solution:

If you need all the information anyway then certainly selecting it once and looping over what you need (particularly since the database is remote and the latency of many calls will add up).

Edit: Looking at the pseudo code: You could use the array of ids from the remote DB to narrow the results grabbed from the local db. Something like:

$log_result = mysql_query('SELECT id, task_id, log_entry, timestamp FROM remote_db WHERE date=X');
$task_lookup = mysql_query('SELECT id, name FROM local_db WHERE id IN taskIdsFromLogQuery');

People are also looking for solutions to the problem: php - Correct HTML status codes for unauthorized access and forbidden access. (using Ajax)

Source