I transfer a large amount of data from several databases to one. As an intermediate step, I copy the data to a file for each data type and db source, and then copy it to a large table in my new database.
The structure is simple in a new table called migrate_data. It consists of an identifier (primary key), type_id (increases in the set of data types), data (a field containing a serialized PHP object containing the data that I am transferring), source_db (refers to the original database, obviously), data_type (determines the type data that we are reviewing).
I created keys and key combinations for everything except the data field. Currently, I have a data field defined as a longtext column. Custom inserts take about 4.8 seconds on average. I was able to trim this to 4.3 seconds using DELAY_KEY_WRITE = 1 in the table.
What I want to know about is there a way to improve performance even further. Perhaps switching to a different type of data column. This is why I am asking about longtext vs text vs blob. Are any of them more effective for this kind of insertion?
Before answering, let me give you a little more information. I send all the data to the insert function, which takes an object, runs it through serialization, and then starts the data insertion. It is also executed using Drupal 6 (and its db_query function).
Any increase in efficiency would be surprising.
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;