Home » 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( 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]
|
|
| | |
| Re: large table to temp table or direct select ? [message #75 is a reply to message #73 ] |
Thu, 17 August 2006 12:11  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
I would not generally rely on the order unless you use order by. It may work but makes application very fragile.
I also would not be iterating over large data sets with
SELECT ... LIMIT N,M - If if you have LIMIT 1000000,10 first milliom rows will be computed and thrown away.
SELECT DISTINCT FIELD1 and looking up by it is probably better approach.
You also might be missing normalization so you need to find distinc t FIELD1 values - having list of different FIELD1 in other table might be good idea.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Goto Forum:
Current Time: Sun Jul 5 16:59:03 EDT 2009
Total time taken to generate the page: 0.02168 seconds |
Members
Search
Help
Register
Login
Home
