<yours> The users_old table has 120,000 rows and the users_new has 910,000 rows. </yours>
If you have no indexes, I'm not at all surprised it takes that long or even longer to get results from a join on MySQL on two tables with this many rows. The join must be completed before results are returned, and that is a long, processor intensive process without indexes. Can e-mail addresses be a primary key on either table? if it can, it should be. If not, it should at least be indexed. Only suggestion I have beyond the indexes is don't use Legacy syntax - that won't speed up the execution, but is just a good habit to have for when you want to do more complex queries. Use this type of join syntax: SELECT users_old.UserId, users_old.Email FROM users_new INNER JOIN users_old ON users_old.Email = users_new.Email aliasing will then save you a bit of typing, but that's secondary - indexing your tables is what you need to improve the performance in this case. Steve H. -----Original Message----- From: Hastie, Christa [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 12:58 PM To: [EMAIL PROTECTED] Subject: ::massive sql query using dbi - please help:: Hello to all! This is my first time posting to this group! But I'm in desperate need of any help! (BTW, thanks for all the emails from contributors to this list..I learn a lot from you guys every day!) I have two tables in a mySQL db, named users_old and users_new, both with UserId and Email columns, no primary keys and no auto-increment columns. The users_old table has numeric values for the UserId while the users_new have NULL values. The users_old table has 120,000 rows and the users_new has 910,000 rows. I'm trying to find a simple, painless way of querying these two tables so I can store the UserId and Email from the users_old table if the Email exists in both tables. Everything I try just continues to run without ever producing any results - it just hangs at the command line when running the standalone query. Perhaps there are just too many rows to compare. I tried writing a simple script using the Perl DBI to just log the results of this massive query in a simple tab delimited flat file so I can load the data into the live database after it finishes....but no luck. Anybody have any suggestions on a better approach? My simple code looks like this: #!/usr/local/bin/perl use DBI; use strict; my($dbh); my($sth); my($exclusive_lock); eval { $dbh = DBI->connect("DBI:mysql:dbname;host=localhost", "dbuser", "dbpassword", {'RaiseError' => 1}); }; if($@) { my($error) = "Error opening Database: $@\n"; print "$error\n"; } my $sth = $dbh->prepare("SELECT users_old.UserId, users_old.Email FROM users_new, users_old WHERE users_old.Email = users_new.Email"); $sth->execute or die "Unable to execute query: $dbh->errstr\n"; my ($row); while($row = $sth->fetchrow_arrayref) { my($data_log) = "/home/chastie/sony_showbiz.txt"; open (DATATEMP, ">>$data_log"); flock (DATATEMP, $exclusive_lock); print LOG "$row->[0]\t$row->[1]\n"; close (LOG); } $sth->finish; $dbh->disconnect; exit; \||||/ (o o) ooO-(_)-Ooo-------------------------------- christa hastie programmer sonypicturesdigitalentertainment www.sonypictures.com -------------------------------------------------
