Arrays vs. second SQL query

Which is more efficient?

[DrDoc]

Two fields: $lastname and $date
$lastname is the only required field

There are two tables: `user` and `service`

---

This is all done using PHP and MySQL:

Step 1) An intial lookup is performed on the `user` table to find all possible matches for $lastname. Unique $username identifiers are extracted.

Step 2) A lookup on the `service` table is performed, matching each $username. A check for matching $date is performed. All matching fields are returned to the user.

Step 3) Step 1 may find matching $lastnames, but step 2 may fail because of an incorrect/omitted $date. In case that happens, I want to extract all matching records from the `service` table, no matter the $date.

Now, here's the real question: Should I store the results from step 1 in temporary arrays, or should I redo the SQL query from step 1 before step 3?

Which one is more efficient? ... both timewise as well as server load?

[jatar_k]

What about selecting them regardless of date and then iterate through the array and do your date distinction in a loop?

<added>fewer sql accesses is better but how many records are we talking about?

Are you using mysql_fetch_array (or something like it) to read the recs into an array? If so you could check the date while putting it into the array and building two arrays then.

[DrDoc]

Well, there are about 10,000 records in the `user` table, and about 75,000 records in the `service` table. Both are expected to be about 10 times that in a year.

What about selecting them regardless of date and then iterate through the array and do your date distinction in a loop?

Well, but the array can be really big then ..

[jatar_k]

good point too many records for that.

I think the extra query might be worth it then I can't think of another method that wouldn't be more intensive.

[DrDoc]

Another part of my reasoning is:

I don't expect step 3 to be necessary in most cases. Consider it a "spell check"... Unfortunately there's no middle way - either "cache" all the data from step 1 (90% useless) or redo the query...

[nosanity]

In that case, you can try caching the data you retrieved the first time into a dbm database, or into another temporary file... might work for you.

noSanity

[jatar_k]

obviously if the date is empty you can select all, the only case you hve to test for is bad date. How would the date be bad can you validate it to remove the possibility for the 3rd step?

[DrDoc]

Yeah, I test for bad date... (Such as Feb 29 on a non-leap year)

If a bad date is entered, it defaults to displaying all entries (with a note about the date error)

[Allen]

Surely if you retrieve the results a second time, then you're creating another array with all the data in, thus keeping the data from the first lookup would be better.

eg.

$query_data = fetch_data_from_db

<do something>

$query_data = fetch_data_from_db

<do something>

is not as effecient as:

$query_data = fetch_data_from_db

<do something>

<do something>

Allen

[jatar_k]

DrDoc, then I don't understand why the 3rd step is necessary.

if (empty($date) ¦¦ $novalidate) {
$sql = "query1"; // all recs query
} else {
$sql = "query2"; // query by date
}

or am I not understanding something?

[DrDoc]

Ok, let me explain...

1) lookup in `user`
2) while($get = mysql_fetch_row($lookup)) {
lookup in `service`
}
3) if 2 returns no matches {
lookup in `service` without matching date
}

Speaking of nothing .. Is there a simple way I can (in step 2)lookup all usernames in an array without doing OR ... OR ... OR, thus eliminating the while loop?

'Cause I just realized that this method is going to perform maybe hundreds of queries in the while loop, and I don't want that...

[DrDoc]

I got a much better idea, but I don't know how to accomplish it.

I want to join the `user` and `service` tables.
Then, I want to find a field 'misc' in `service` that matches $date, and field 'persinfo' in `user` that matches $lastname. Both rows should have identical 'username' fields.

Don't know if what I just said makes sense?

[jatar_k]

what's the common field between the two tables?

[edited by: jatar_k at 8:20 am (utc) on Mar. 15, 2003]

[DrDoc]

username

[jatar_k]

Since you're around I unedited my post and added a new one so you would see it.

maybe,

select * from user left join service on user.username=service.username where service.misc=$date and user.persinfo=$lastname

I think that's right

[DrDoc]

Thank you very much! I will test it :)

[DrDoc]

It worked perfectly! :)

[jatar_k]

glad to hear it, wasn't totally sure ;)