| alorama 2005-09-26, 6:55 pm |
| 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 have also written a simple process procedure to try an read the mysql
table and dump it into a topspeed table but, the program crashes before
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
|