# large table  to temp table or direct select ?

**URL:** <https://forums.percona.com/t/large-table-to-temp-table-or-direct-select/65>\
**Category:** Other MySQL® Questions\
**Created:** [August 17, 2006, 9:57am UTC](https://forums.percona.com/t/large-table-to-temp-table-or-direct-select/65 "2006-08-17T09:57:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mkirank](https://avatars.discourse-cdn.com/v4/letter/m/13edae/32.png) [@mkirank](https://forums.percona.com/u/mkirank)\
**Post date:** [August 17, 2006, 9:57am UTC](https://forums.percona.com/t/large-table-to-temp-table-or-direct-select/65/1 "2006-08-17T09:57:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 17, 2006, 10:15am UTC](https://forums.percona.com/t/large-table-to-temp-table-or-direct-select/65/2 "2006-08-17T10:15:40Z")

</div>

And so what is your question ?

---

<div class="post-metadata">

**Author:** ![mkirank](https://avatars.discourse-cdn.com/v4/letter/m/13edae/32.png) [@mkirank](https://forums.percona.com/u/mkirank)\
**Post date:** [August 17, 2006, 10:26am UTC](https://forums.percona.com/t/large-table-to-temp-table-or-direct-select/65/3 "2006-08-17T10:26:18Z")

</div>

what would be the best way to do this,

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

OR

1. Select distinct of FIELD1 from large\_table  
and  
for each member  
select \* from large\_table where FIELD1=member.