Which is the better method??

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Let me set this up for y'all...

I have 8 mySQL databases with over 100K records, which include a phone  
number field. I call these the storage tanks. They're labeled db1 - db8.

Then I have 1 mySQL database with 1-2K records, they too have a phone  
number field. This database is labeled dbX

The task is to see whether each phone number from each record in dbX is  
or is not in db1 or db2 or ... db8. Ultimately deduping dbX from db1-8.

I have two methods to attack this situation.

Method 1:
  Reiterate through each record in dbX and call a function that returns  
the number of rows in a result from each tank. Each function is a SQL  
call to SELECT * FROM db# WHERE phonenumber=$phonenumber. Example -
  $result = mysql_result();
  while ( mysql_fetch_row($result))
   $1 = function1($phonenumber)
   $2 = function2($phonenumber)
   $8 = function8($phonenumber)
Thus having the number of times $phonenumber is in each tank, allowing a  
wild if, else if loop to determine if $phonenumber exists in db1-8.

Method 2:
  Same thing, but instead of each function being a SQL call, have it be  
an in_array() check returning 1 for true or 0 for false. Then doing the  
same wild if, else if loop to determine if $phonenumber exists or not.

So, I guess my ultimate question is... Is it better(faster) to make 1-2K  
* 8(db1, db2...db8) SQL queries or fill 8 arrays and perform in_array()  
checks on those filled arrays.

I have a feeling that Method 2 might be faster... but I'm hoping the  
community can help.

Feel free to ask more details.


Re: Which is the better method??

Frank [GOD] wrote:
Quoted text here. Click to load it

If your ultimate aim is purely to delete the duplicate info, why not do
the whole thing in SQL?


FROM db1, db2, db3, db4, db5, db6, db7, db8
WHERE db1.phoneNum = db2.phoneNum OR db1.phoneNum = db3.phoneNum ...

FROM db2, db3, db4, db5, db6, db7, db8
WHERE db2.phoneNum = db3.phoneNum OR db2.phoneNum = db4.phoneNum ...

FROM db3, db4, db5, db6, db7, db8
WHERE db3.phoneNum = db4.phoneNum OR db3.phoneNum = db5.phoneNum ...

A total of 7 queries, and no PHP processing (other than to create the
query strings).



Site Timeline