Welcome to WebmasterWorld Guest from 3.226.251.205

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Arrays vs. second SQL query

Which is more efficient?

     
6:56 am on Mar 14, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


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?

6:58 am on Mar 14, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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.

7:19 am on Mar 14, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


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 ..

7:22 am on Mar 14, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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.

7:43 am on Mar 14, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


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...

7:46 am on Mar 14, 2003 (gmt 0)

Junior Member

10+ Year Member

joined:Dec 6, 2001
posts:99
votes: 0


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

7:49 am on Mar 14, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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?
7:58 am on Mar 14, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


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)

10:50 am on Mar 14, 2003 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 14, 2002
posts:118
votes: 0


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

3:58 pm on Mar 14, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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?

7:53 pm on Mar 14, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


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...

7:27 am on Mar 15, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


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?

8:08 am on Mar 15, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


what's the common field between the two tables?

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

8:18 am on Mar 15, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


username
8:21 am on Mar 15, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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

8:25 am on Mar 15, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


Thank you very much! I will test it :)
8:39 am on Mar 15, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


It worked perfectly! :)
8:39 am on Mar 15, 2003 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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