0

I have table structure like below:

CREATE TABLE IF NOT EXISTS `dnddata` (
  `numbers` varchar(10) NOT NULL,
  `opstype` char(1) NOT NULL,
  PRIMARY KEY (`numbers`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (numbers)
PARTITIONS 20 */;

When I inserting data into this table taking long time.

Taking 50 min to insert 3 million records.

I have used load data and insert into dnddata SELECT * from another_myisamtable.

Both methods are taking nearly equal time.

Same table without partition taking less than 2 min.

I have to insert data of 50 million records.

Please suggest which is the best way to insert this many records.

Dharman
  • 30,962
  • 25
  • 85
  • 135
sankar.suda
  • 1,097
  • 3
  • 13
  • 26

1 Answers1

3

The funny thing about partitioned tables is that when you try and INSERT into just one partition, you have to go and lock down (or at least "pay the lock a visit") all partitions. So - more partitions == more locks.

You can read more about it in Giuseppe Maxia's blog.

Although said post discusses the INSERT of single rows, the same problem applies to multiple rows as well.

Since you are using MyISAM, actual locks are taken. You may choose to try the same with an InnoDB table of the same format.

Shlomi Noach
  • 9,073
  • 1
  • 23
  • 20
  • Thanks shlomi, locking is not a problem for us. we insert the records while server is idle. my requirement is insert in less time.. – sankar.suda Jul 18 '12 at 09:17
  • I understand. Nevertheless this locking takes time in itself, regardless of possible contention. – Shlomi Noach Jul 18 '12 at 10:02