Review: MySQL database second data import and load tests
Feature: The MySQL data import tools prove to be both fast and versatile

In the first review of MySQL database loading and then testing of its MySQL Migration toolkit, I found MySQL to be fairly versatile in loading in the numerically oriented Baseball database. But now the Wordweb database poses a more sophisticated challenge with CLOB fields having embedded punctuation such as comma and single quote - ' and double quote - ". These embedded characters become a problem when a CLOB field is followed by another field because then for example, a comma embedded in the CLOB field can be misinterpreted as the field delimiter. But if you use either single quote or double quote as the enclosed by delimiter - goes awry for about 200-300 of the rows which contain those characters embedded as part of the CLOB field.

Fortunately MySQL's LOAD DATA INFILE command is more than equal to the task providing for just about any character as the FIELDS ENCLOSED BY '`' (a backquote character is being used here) allows. This means then that the onus is to get a dump of the datafile with fields enclosed by backquote or tilde. Or one has to replace the current delimiter, say single quote, with a backquote or tilde using an adept editor like Notepad++. Fortunately, we did not have to do this because all the the Wordweb tables had their CLOBS as the last field and LOAD DATA INFILE was smart enough to pickup all the data to the end of line even when there were embedded commas. The following query shows that:
This query shows that the LOAD picked up embedded double quotes but also other punctuation including commas. By the way the LOAD is impressively fast, 126,000 records were done in less than a second.

However, it is welcome to know that the LOAD DATA INFILE command has available not just the FIELD ENCLOSED BY clause but also a FIELD ESCAPED BY clause as well. So for example, I used backslash as an escape character for commas when creating a copy of an Access NUTRITION database . The result was that the LOAD DATA INFILE command was able to read the NUTRITION database with no problems. Since the MYIMPORT command line tool uses LOAD DATA INFILE, I assume it is equally adept at reading in data.

In general I found like DB2, MySQL's data import commands are very solid. But I wondered how good third party tools like SQLyog which are very impressive elsewhere for MySQL admin and querying would do. Well I should have guessed that third party toolmakers would take advantage of the strong MySQL data loaders:

Although users have to be careful in using the Enclosed By and Escaped By clauses (don't use them when not needed as the last field to be imported as here), again SQLyog's import speed was also very fast - they say 0 ms for 126,385 rows and it seemed like that.

Summary

So MySQL gets top marks along with DB2 for ease of data loading and importing. MySQL's loader was one of the fastest I observed - admittedly in no tests were the number of records in a table to be loaded more than a half million rows. In contrast, the MySQL Migration utility was a bit disappointing because it hiccupped on some datatypes and simply appears not yet ready to pickup constraints, triggers, keys, and stored procedures from other databases in a clean and thorough import process. So give good marks to MySQL but also look to improvement in the Migration Toolkit.