Posted by Oli Filth on 11/29/05 15:54

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

[Back to original message]