MySQL Forums
Forum List  »  Perl

Problem with timeout issues with mysql code in Perl
Posted by: Richard Jones
Date: December 11, 2008 06:48AM

Hello everyone. We have a membership website that we made the mistake of using Usernames to tie everything together with, instead of MemberId's... Now, 2 years later, we have nearly 50k members and grow by about 150+ per day.

We did not allow changing usernames because of the way the website was developed in a hurry 2 years ago. Now we want members to be able to change their usernames.

The problem is that our database has 310 tables. around 200+ of them have the Members Usernames.
So I wrote this code:

my @_mb = $dbh->tables;
foreach my $_k (@_mb) {
	my $_sql = "select * from $_k";
	my $sth = $dbh->prepare($_sql);
	$sth->execute();
	my @_mb2 = @{$sth->{NAME}};
	foreach my $_k2 (@_mb2) {
		if($_k2 =~ /username/i) {
			my $_countThis = $dbh->selectrow_array(qq{select count(*) from $_k where `$_k2` = ?}, undef, $_uname);
			$_k =~ s/\`//g;
			$_places2change .= $_k . ' -> ' . $_k2 . ' -> ' . $_countThis . br() . "\n";
			$_count += $_countThis;
		}
	}
	$sth->finish();
}

What that does is in my admin interface, where I am testing the code, that takes the username we are wanting to change in the form and validates it is real, then it goes and gets a list of all the tables. Then it runs through that array, and uses each table name to do a count of all the locations that have a column with 'username' in it. It then counts the entries for the username we are validating, then it puts out a confirmation message, like this example:

'
Ok, I have validated the username you want to change is real, I found the entry $_count times. Also, I have listed all the locations that the username exists throught the database. Below you will find a form where you can choose a new username.
'

Then where it lists the locations it puts them like this example:
table name -> Column name -> Times in that table
comm_members_122007 -> USERNAME -> 1

Here is my problem... that works, but it takes like 3 minutes and 10 seconds everytime...

Is there a faster way to do this?
I'm trying to automate this. I did have it where only us administrators could change usernames using my admin interface, and I created an array that had all the tables that had the usernames, something like this:

my @db_fields = (
 { name => "members", field=>"USERNAME", dbname=>"websitedb"},
 { name => "addresses", field=>"username", dbname=>"websitedb"},
);

But that array got really large, so I was afraid since it was stored in the vars file that it would just bog down the memory of the server, plus everytime we backed up the tables in the database I would have to update the array with the new table names...

I would appreciate any feedback you can offer to help me figure out a better way to automate this for our members to be able to change their own usernames.

thanks,
Richard

Options: ReplyQuote


Subject
Written By
Posted
Problem with timeout issues with mysql code in Perl
December 11, 2008 06:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.