Hi, I'm fairly new to MySQL with Perl, a while ago someone showed me how to retrieve and insert and thats all i've done since, but now i've started using 2,000,000+ records and my scripts slow down heaps.

I was wondering if someone could tell me if i'm doing things the right way (in terms of interfacing with the database) or if it is just my scripting that needs work :)

Here's an example of how I do things...
#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# Database Definition
my $dbh = &DB_OPEN('myDatabase','localhost','3306','user','password');
sub DB_OPEN {
  my ($db_name,$host_name,$port,$db_user,$db_pass,) = @_;
  $database = "DBI:mysql:$db_name:$host_name:$port";
  $dbh = DBI->connect($database,$db_user,$db_pass);
} # end-sub

# Do a typical select that retrieves many results...
my %DataHash;
my $sth = $dbh->prepare(" select ID, Data from table_name where Data=\
+"value\" ");
$sth->execute;
while ( my ( $ID, $Data) = $sth->fetchrow ) {
  $CountryDisplay{$ID} = "$Data";
} # end-while
$sth->finish;

# Do the updating...
foreach (keys (%Data)) {
  $dbh->do(" update new_table set newData=\"$Data{$_}\" where newID=$_
+ ");
} # end-foreach

# Disconnect
$dbh->disconnect;

exit;

[download]

I store the data in a hash before inserting it into the new_table because that seems to work quicker for me.

Any help would be appreciated and if you need any more info (i.e. an actual script) just let me know :)

Cheers,
Reagen

In reply to How to Speed up MySQL w/ Perl by rsiedl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.