title: MySQL, which is more efficient longtext, text, or blob? Improving insert efficiency
body: <p>I am in the process of migrating a large amount of data from several databases into one. As an intermediary step I am copying the data to a file for each data type and source db and then copying it into a large table in my new database.</p>
  
  <p>The structure is simple in the new table, called migrate_data. It consists of an id (primary key), a type_id (incremented within the data type set), data (a field containing a serialized PHP object holding the data I am migrating), source_db (refers to the source database, obviously), data_type (identifies what type of data we are looking at).</p>
  
  <p>I have created keys and key combinations for everything but the data field. Currently I have the data field set as a longtext column. User inserts are taking about 4.8 seconds each on average. I was able to trim that down to 4.3 seconds using DELAY_KEY_WRITE=1 on the table.</p>
  
  <p>What I want to know about is whether or not there is a way to improve the performance even more. Possibly by changing to a different data column type. That is why I ask about the longtext vs text vs blob. Are any of those more efficient for this sort of insert?</p>
  
  <p>Before you answer, let me give you a little more information. I send all of the data to an insert function that takes the object, runs it through serialize, then runs the data insert. It is also being done using Drupal 6 (and its db_query function).</p>
  
  <p>Any efficiency improvements would be awesome.</p>
  
  <p><strong>Current table structure:</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `migrate_data` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `type_id` int(10) unsigned NOT NULL DEFAULT '0',
    `data` longtext NOT NULL,
    `source_db` varchar(128) NOT NULL DEFAULT '',
    `data_type` varchar(128) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `migrated_data_source` (`source_db`),
    KEY `migrated_data_type_id` (`type_id`),
    KEY `migrated_data_data_type` (`data_type`),
    KEY `migrated_data_id__source` (`id`,`source_db`),
    KEY `migrated_data_type_id__source` (`type_id`,`source_db`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1;
  </code></pre>