Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Which is the better method??
- Frank [GOD]
November 29, 2005, 5:49 am
rate this thread
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.
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.
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??
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
Note: NOT TESTED!