MySQL Performance: MySQL => large table to temp table or direct select ?

large table to temp table or direct select ? [message #71]

Thu, 17 August 2006 11:27

mkirank
Messages: 2
Registered: August 2006

Junior Member

I have a table with the following structure given below and it has around 9 million records, I have created a script in perl that reads each record in this table does some processing and Insert it into many tables.

the field FIELD1 is the different members so for calculation purpose I need to order by that field ,what would be the best way to do this, There are around 10,000 distinct members (FIELD1).

1. Insert into temp_table as select * from large_table order by FIELD1
and then do select * from temp table limit 0,50000 (in a loop in the script) this way I get the FIELD1 in order

2. Select distinct of FIELD1 from large_table
and
for each member
select * from large_table where FIELD1=member.

CREATE TABLE large_table (
`FIELD0` varchar(4) NOT NULL default '',
`FIELD1` varchar( Cool

NOT NULL default '',
`FIELD2` varchar(12) NOT NULL default '',
`FIELD3` varchar(12) NOT NULL default '',
`FIELD4` timestamp(14) NOT NULL,
`FIELD5` varchar(60) NOT NULL default '',
`FIELD6` varchar(60) default NULL,
`FIELD7` varchar(255) default NULL,
`FIELD8` varchar(60) default NULL,
`FIELD9` varchar(60) default NULL,
`FIELD10` char(2) default NULL,
`FIELD11` char(3) default NULL,
`FIELD12` varchar(15) default NULL,
`FIELD13` varchar(60) default NULL,
`FIELD14` varchar(60) default NULL,
`FIELD15` varchar(60) default NULL,
`FIELD16` varchar(60) default NULL,
`FIELD17` varchar(60) default NULL,
`FIELD18` char(2) default NULL,
`FIELD19` varchar(30) default NULL,
`FIELD20` varchar(4) default NULL,
`FIELD21` smallint(5) unsigned default NULL,
`FIELD22` smallint(5) unsigned default NULL,
`FIELD23` date default NULL,
`FIELD24` date default NULL,
`FIELD26` varchar(20) default NULL,
`FIELD27` varchar(10) default NULL,
`FIELD29` char(2) default NULL,
`FIELD30` varchar(128) default NULL,
`FIELD31` varchar(128) default NULL,
`FIELD32` varchar(60) default NULL,
`FIELD33` varchar(15) NOT NULL default '',
`FIELD34` varchar(15) NOT NULL default '',
`FIELD35` text,
`FIELD36` text,
`FIELD38` datetime default NULL,
`FIELD39` text,
`FIELD41` varchar(10) NOT NULL default '',
PRIMARY KEY (`FIELD0`,`FIELD1`,`FIELD2`),
KEY `lastedit` (`FIELD4`),
KEY `Stat_dbp` (`FIELD1`,`FIELD24`,`FIELD29`),
KEY `LEd` (`FIELD1`,`FIELD4`),
KEY `FIELD41` (`FIELD41`),
KEY `Origb` (`FIELD38`),
KEY `FIELD34_2` (`FIELD34`,`FIELD30`,`FIELD29`,`FIELD24`),
KEY `FIELD1_2` (`FIELD1`,`FIELD24`,`FIELD29`,`FIELD23`),
KEY `FIELD1_FIELD4` (`FIELD1`,`FIELD4`),
KEY `DDteBy` (`FIELD1`,`FIELD24`),
KEY `AlDte` (`FIELD23`),
KEY `FIELD33` (`FIELD33`),
KEY `OrigBDte` (`FIELD1`,`FIELD38`),
KEY `FIELD1` (`FIELD1`,`FIELD29`,`FIELD23`),
KEY `FIELD34` (`FIELD34`),
KEY `FIELD2` (`FIELD2`),
KEY `FIELD3` (`FIELD3`),
KEY `DDte` (`FIELD24`),
KEY `ADte` (`FIELD1`,`FIELD23`),
KEY `FIELD5` (`FIELD5`),
KEY `FIELD23` (`FIELD23`,`FIELD5`)
) TYPE=MyISAM MAX_ROWS=12000000 PACK_KEYS=1

[Updated on: Thu, 17 August 2006 11:32]

Report message to a moderator