I am using a perl script to update a MySQL database. I am
reading in an input file in delimited format. All I need
to do is pull of the first field (an ID) and mark a flag
in the database for that ID. I've done that by preparing
a statement similar to:

$statement="update table_name set flag='Y' where trim(ID)=? and
company='$company' and etc...";
$sth=$dbh->prepare($statement) or die...;

# ID's have already been read in to an array...

foreach (@ID_array) {
$id = &trim_whitespace($_);
$sth->execute($id);
}

# etc...

Normally, this would run fairly quickly (indexes are set
appropriately) but the trim(id)= part of the statement
kills MySQL's use of indexes apparently. I was using that
because some of the fields in the database contain spaces,
some don't. Some of the fields in the input file contain
spaces, some don't... so to compare, I was trimming on both
ends first.


Does anyone have a suggestion on a different way of running
multiple update statements? or is there a better way around
the whitespace problem? other suggestions? This piece of the
application needs to run very quickly (it has to be repeated
several times with medium to large input files).


Thanks for your help. You can post replies or email me:
davidr@cschristian.com

Happy Holidays!