mysql> show create table single_words;

| single_words | CREATE TABLE `single_words` (
  `word_id` int(10) unsigned NOT NULL auto_increment,
  `word` varchar(30) binary default NULL,
  `in_stoplist` enum('y','n') default 'n',
  PRIMARY KEY  (`word_id`)
) TYPE=MyISAM |
1 row in set (0.00 sec)

mysql> show create table single_frequency;

| single_frequency | CREATE TABLE `single_frequency` (
  `word` varchar(30) binary default NULL,
  `frequency` int(5) unsigned default NULL,
  `word_id` int(10) unsigned NOT NULL default '0',
  `doc_id` int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM |

1 row in set (0.00 sec)

mysql> EXPLAIN select doc from doc where doc_id=5;
+-------+-------+---------------+---------+---------+-------+------+-------+
| table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+-------+-------+---------------+---------+---------+-------+------+-------+
| doc   | const | PRIMARY       | PRIMARY |       4 | const |    1 |       |
+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN select word_id, word from single_words where word = 'I';
+--------------+------+---------------+------+---------+------+------+-------------+
| table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+--------------+------+---------------+------+---------+------+------+-------------+
| single_words | ALL  | NULL          | NULL |    NULL | NULL | 1431 | Using where |
+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

	
#connect to the databases	
	my $dbh1 = DBI->connect("dbi:mysql:corpus_stats","sid" , $password);
	my $dbh = DBI->connect("dbi:mysql:concordance","sid" , $password);
	
#select max,min record id from document table for using as loop counter 
	my @id  = $dbh1->selectall_arrayref("select min(rec_id),max(rec_id) from scotts_data");
	
	batch : for ($k= ($id[0][0][0]);$k<= ($id[0][0][1]);$k++) {	
#select the particular records corresponding to loop counter
	
my @string = $dbh1->selectall_arrayref("select rec_id,data from scotts_data where rec_id = $k");
	
	$doc = "$string[0][0][1]";

#split the doc in indivdual words and pass through regex, build required collocations and find unique combinations	
	
@single = split(' ',$doc);

	foreach (@single)
	{
		$_=~ s/[!^~#&*_+=|?,'.;:]//g;
	}

	for ($i = 0; $i <= ($#single-1); $i++)
	{
		push(@double,$single[$i]." ".$single[$i+1]);
	}   

	for ($i = 0; $i <= ($#single-2); $i++)
	{
		push(@triple,$single[$i]." ".$single[$i+1]." ".$single[$i+2]);
	}   
	
	for ($i = 0; $i <= ($#single-3); $i++)
	{
		push(@quad,$single[$i]." ".$single[$i+1]." ".$single[$i+2]." ".$single[$i+3]);
	}   

	@single_uniq = grep { ! $seen{$_} ++ } @single;
	@double_uniq = grep { ! $seendouble{$_} ++ } @double;
	@triple_uniq = grep { ! $seentriple{$_} ++ } @triple;
	@quad_uniq = grep { ! $seenquad{$_} ++ } @quad;
	


# for each unique element element select word,word_id from words table
	
single:for ($j=0;$j<=$#single_uniq;$j++){
	
		$frequency = ($seen{$single_uniq[$j]});
		my @row = $dbh->selectall_arrayref("select word,word_id from single_words where word = '$single_uniq[$j]'");	
		
#if query returns NULL then insert value of unique element and its frequency in words and frequency tables

		if($row[0][0][0] eq "")
			{
			$dbh->do ("insert into single_words values('','$single_uniq[$j]','n')");
			my $max1 = $dbh->selectall_arrayref("select max(word_id) from single_words");
			$dbh->do ("insert into single_frequency values('$single_uniq[$j]',$frequency,'$max1->[0][0]',$string[0][0][0])"); 			
			}			
#if the a value is returned then insert value of unique element and its frequency in only the frequency table
		else
			{
			$dbh->do ("insert into single_frequency values('$row[0][0][0]',$frequency,'$row[0][0][1]',$string[0][0][0])");
			}
		
undef @row;		

	}