Which is the better method??

[Frank [GOD]]

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.

Frank

[Oli Filth]

Frank [GOD] wrote:

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.

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

e.g.

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

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

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

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

Note: NOT TESTED!
--
Oli