Ok. I changed my code to do a Mysql query.
I have 2 files DB and a MySQL Table. The mysql table is constructed with 3 column: chrom, start, end.
DB has 7 columns. For each line in DB, I want to count the number of times that col2 matches chrom and start is BETWEEN col3 and col4. Then I want to print that value in DB col8. Here is the code I have:
#!/usr/bin/perl
# PERL MODULE
use DBI;
my $DB_NAME = "H3K36me3";
my $DB_USER = "root";
my $DB_PASS = "password";
my $dbh = DBI->connect("DBI:mysql:$DB_NAME","$DB_USER","$DB_PASS");
open (FILE,"@ARGV[0]")||die "usage: perl MySQL.query.pl <DB> ";
@array=<FILE>;
close(FILE);
foreach $line (@array){
my ($Gene,$chr_id,$left,$right,$Strand,$ExonCount,$SizeKB)=split(/
+\t/,$line);
$sql = "select count(*) from H3K36me3 where chrom =\"$chr_id\" AND
+ start between $left AND $right";
$sth= $dbh->prepare($sql);
@Count=$sth->execute()||die "problem Here!\n";
@Count=$sth->fetchrow();
while (@Count = $sth->fetchrow()) {
print "@Count[0]\n";
}
}
The problem is that I can print it to the screen and it will slowly work (the MySQL table is 20 million rows), but when I go to print to file with ' perl MySQL.query.pl DB > Out.txt' it stops around ~6000 rows. It actually stops in a wierd way, like if the actual count is 30, it will only print 3.
How do I deal with this?