MySQL, which is more efficient longtext, text, or

Question

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.

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).

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.

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?

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).

Any efficiency improvements would be awesome.

Current table structure:

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;

Answer 1

The various text/blob types are all identical in storage requirements in PHP, and perform exactly the same way, except text fields are subject to character set conversion. blob fields are not. In other words, blobs are for when you're storing binary that MUST come out exactly the same as it went in. Text fields are for storing text data that may/can/will be converted from one charset to another.