# load data overhead

**URL:** <https://forums.percona.com/t/load-data-overhead/2028>\
**Category:** Other MySQL® Questions\
**Created:** [April 1, 2013, 6:15am UTC](https://forums.percona.com/t/load-data-overhead/2028 "2013-04-01T06:15:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![TimeKeeper](https://avatars.discourse-cdn.com/v4/letter/t/9e8a1a/32.png) [@TimeKeeper](https://forums.percona.com/u/TimeKeeper)\
**Post date:** [April 1, 2013, 6:15am UTC](https://forums.percona.com/t/load-data-overhead/2028/1 "2013-04-01T06:15:03Z")

</div>

I’m trying to restore data from text file (been generated as ‘select … into outfile …’) in a Percona server version: 5.5.28-29.3.

I launched query ‘load data infile ‘…’ ignore into table tbl\_new’, but it takes already 5 days and I can’t understend what happening?

The ‘old’ table takes 38.6 GiB (information of phpmyadmin), data file size is 18.7 GiB, but the new table already takes 46.5 GiB and query still in progress!

In adddition I chenged table schema of the new one and drop primary key autoincrement (int(11)) and set to primary the new field `hash_d` - bigint(20), but the old table has both fields and id - autoincrement (int(11)) and `hash_d` - bigint(20). Table size about 220M rows.

CREATE TABLE `tbl_old` ( `id_d` int(11) unsigned NOT NULL AUTO\_INCREMENT, `hash_d` bigint(20) NOT NULL DEFAULT ‘0’, `name_d` varchar(300) NOT NULL, `id_z` smallint(5) unsigned NOT NULL, `hash_p` bigint(20) NOT NULL DEFAULT ‘0’, `ins_date` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP, `st_b` smallint(5) unsigned NOT NULL, `s_type` int(11) NOT NULL DEFAULT ‘0’, `id_st` int(11) DEFAULT ‘0’, `r_code` smallint(3) NOT NULL DEFAULT ‘-1’, PRIMARY KEY (`id_d`), UNIQUE KEY `idx_hd` (`hash_d`), KEY `id_st` (`id_st`), KEY `id_z` (`id_z`), KEY `idx_st_b` (`st_b`), KEY `idx_p` (`hash_p`)) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE TABLE `tbl_new` ( `hash_d` bigint(20) NOT NULL DEFAULT ‘0’, `name_d` varchar(300) NOT NULL, `id_z` smallint(5) unsigned NOT NULL, `hash_p` bigint(20) NOT NULL DEFAULT ‘0’, `ins_date` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP, `st_b` smallint(5) unsigned NOT NULL, `s_type` int(11) NOT NULL DEFAULT ‘0’, `id_st` int(11) DEFAULT ‘0’, `r_code` smallint(3) NOT NULL DEFAULT ‘-1’, PRIMARY KEY (`hash_d`), KEY `id_st` (`id_st`), KEY `id_z` (`id_z`), KEY `idx_st_b` (`st_b`), KEY `idx_p` (`hash_p`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

---

<div class="post-metadata">

**Author:** ![miguelangelnieto](https://avatars.discourse-cdn.com/v4/letter/m/b9e5f3/32.png) [@miguelangelnieto](https://forums.percona.com/u/miguelangelnieto)\
**Post date:** [April 2, 2013, 4:12am UTC](https://forums.percona.com/t/load-data-overhead/2028/2 "2013-04-02T04:12:40Z")

</div>

Can you show us the output of show engine innodb status and show full processlist?

If you have changed the PK from INT to BIGINT then the table will be bigger in size. INT is 4 bytes and BIGINT is 8 bytes. Also, take in account that in InnoDB every secondary key has the value of the PK attached to it. So now, every KEY will have a hidden second column with a BIGINT of 8 bytes.

---

<div class="post-metadata">

**Author:** ![TimeKeeper](https://avatars.discourse-cdn.com/v4/letter/t/9e8a1a/32.png) [@TimeKeeper](https://forums.percona.com/u/TimeKeeper)\
**Post date:** [April 2, 2013, 5:15am UTC](https://forums.percona.com/t/load-data-overhead/2028/3 "2013-04-02T05:15:46Z")

</div>

Thanks alot!  
This fact, about hidden column in index, I did not know. Unfortunatly I can’t show engine status because the query already finished. But when I tested different types of data and structures, I got contradictory results. The stuctures above are good example of your explanation of key’s structure, but when I tryed anothe structires, like

REATE TABLE `tbl_int_key` ( `id` int(10) unsigned NOT NULL AUTO\_INCREMENT, `field_1` bigint(20) NOT NULL DEFAULT ‘0’, `field_2` bigint(20) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`id`), UNIQUE KEY `idex_fiealds` (`field_1`,`field_2`), KEY `idx_domainTo` (`field_2`,`field_1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8CREATE TABLE `tbl_d_bigint_key` ( `field_1` bigint(20) NOT NULL DEFAULT ‘0’, `field_2` bigint(20) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`field_1`,`field_2`), KEY `idx_domainTo` (`field_2`,`field_1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

The first one (tbl\_int\_key) table was almost in 40% bigger then the second one (tbl\_d\_bigint\_key), whenever the primary key must be bigger in the second table (tbl\_d\_bigint\_key).

Is the answer lies in the quantity of keys in the tables?