MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Nurullah Akkaya Date:January 13 2007 6:13pm
Subject:Reading large(600k-1000k) number of records.
View as plain text  
i have the following table in my database i am trying select postings  
based on wordId whole table has around 200 million records.

DROP TABLE IF EXISTS `postingList`;
CREATE TABLE `postingList` (
   `wordId` MEDIUMINT UNSIGNED NOT NULL,
   `docId` INT UNSIGNED NOT NULL,
   `docPosition` smallint UNSIGNED NOT NULL,
   `bold` tinyint unsigned,
   `header` tinyint unsigned,
   `url` tinyint unsigned,
   `anchorId` MEDIUMINT UNSIGNED,
   `upperCase` tinyint unsigned,
   INDEX  (`wordId`),
   INDEX  (`docId`),
   INDEX  (`anchorId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='list postings';

i read these in to my application using result set streaming since i  
don't know how many i'll read i do not want to run out of memory i  
have one thread reading results from database and two threds  
processing the results read.

i can not spread reads across disks or use mysql cluster. what i was  
wondering is if i squeeze those 3 tinyints into int and read just  
that will it improve performance i only use mysql to store data all  
calculations are done in memory are all the postings are read.

i have increased the key_buffer size but this problem occurs during  
the first read so key_buffer wasn't very helpful.



Nurullah Akkaya
nurullah_akkaya@stripped

Blooby.com
Tel: +1 (256) 270 4091




Thread
Reading large(600k-1000k) number of records.Nurullah Akkaya13 Jan
  • Re: Reading large(600k-1000k) number of records.mos15 Jan