Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > Programming languages > cannot read large mysql table end to end
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 21st October 15:25
alorama
External User
 
Posts: 1
Default cannot read large mysql table end to end


I have a mysql table described below. I have 380,000 records in it. I
realize there is a large MARC_DATA text field in the table. which is
the most important field I do need to read.

I can read the file using prop:sql commands as follows.

SELECT SQL_NO_CACHE * FROM `my_marc` USE INDEX (PRIMARY) ORDER BY
`MRC_ID` ASC LIMIT 0 ,5000

SELECT SQL_NO_CACHE * FROM `my_marc` USE INDEX (PRIMARY) ORDER BY
`MRC_ID` ASC LIMIT 5000 ,5000

SELECT SQL_NO_CACHE * FROM `my_marc` USE INDEX (PRIMARY) ORDER BY
`MRC_ID` ASC LIMIT 10000 ,5000

And so on. The problem is that as I go along these queries take longer
and longer as I go into the table. It appears that mysql is re
scanning the entire table each time I send a sql command. All of the
data is sent to a buffer on my client machine before it will process
the query. What can I do?


CREATE TABLE my_marc (
MRC_ID int(11) NOT NULL auto_increment,
ORG tinyint(3) NOT NULL default '0',
TITLENUM int(11) NOT NULL default '0',
UPD tinyint(3) NOT NULL default '0',
TEMP_ILL tinyint(3) NOT NULL default '0',
LEADER varchar(9) NOT NULL default '',
TITLEDISPLAY varchar(120) NOT NULL default '',
AUTHOR varchar(40) NOT NULL default '',
YEAR varchar(4) NOT NULL default '',
ISBN varchar(15) NOT NULL default '',
STATUS char(1) NOT NULL default '',
ASTAMP char(3) NOT NULL default '',
EDITDATE date NOT NULL default '0000-00-00',
EDITTIME time NOT NULL default '00:00:00',
MARC_DATA text NOT NULL,
HOT tinyint(2) NOT NULL default '0',
RESERVEABLE tinyint(2) NOT NULL default '1',
DISPLAYRECORD tinyint(2) NOT NULL default '1',
ILL tinyint(2) NOT NULL default '1',
MAJOR tinyint(2) NOT NULL default '0',
INTERMEDIATE tinyint(2) NOT NULL default '0',
MINOR tinyint(2) NOT NULL default '0',
MTYPE tinyint(3) NOT NULL default '0',
STITLE varchar(30) NOT NULL default '',
PRIMARY KEY (MRC_ID),
UNIQUE KEY MRC_KEY (ORG,TITLENUM),
KEY UPD (UPD)
) TYPE=MyISAM MAX_ROWS=4294967295 AVG_ROW_LENGTH=25182;


I can even add one record to my tps file. If there is nothing I can do
to get this working fairly quickly. Can you suggest some type of work
around?

Thanks

Allen Patrick
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666