Mysql – Converting CHAR to BIT

Large PRIMARY KEYs and UNIQUE KEYs are a very bad idea for InnoDB. If you want a real performance boost for subsequent retrieval, you must use smaller PRIMARY KEYs. Why?

• The smaller the PRIMARY KEY, the better things will be
• Secondary indexes have a primary key associated with each secondary key. Bigger PRIMARY Key, bigger Secondary Index. and Vice-Versa
• Caching improves with smaller primary keys

Here is what you should do with 64-char keys

STEP01) Create a Key Table for 64-character keys

Start with a table that will hold the 64-character key and associate it with an auto incremented field

CREATE TABLE big64keytable
(
    bigkey   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    big64key CHAR(64),
    PRIMARY KEY (bigkey),
    UNIQUE KEY (big64key)
) ENGINE=MyISAM;

STEP02) Create a Stored Function to Get numeric ID for the 64-character key

First, create the SQL to INSERT your 64-character key and then retrieve the numeric ID

SET @givenkey = '8g4gbf3g7b5gf4n3gfn8g927534';
INSERT IGNORE INTO big64keytable (big64key) VALUES (@givenkey);
SELECT bigkey FROM big64keytable WHERE big64key = @givenkey;

Now take this simple retrieval algorithm and place it in a Stored Function

DELIMITER $$

DROP FUNCTION IF EXISTS `GetBigKey` $$
CREATE FUNCTION `GetBigKey` (GivenKey CHAR(64)) RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN

    DECLARE rv INT UNSIGNED;

    INSERT IGNORE INTO big64keytable (big64key) VALUES (GivenKey);
    SELECT bigkey INTO rv FROM big64keytable WHERE big64key = GivenKey;
    RETURN rv;

END $$

DELIMITER ;

Going forward, simply generate a numeric unsigned ID (4 bytes ) for each 64-character key.

Here are two of my past posts in the DBA StackExchange on other large PRIMARY KEY issues

• Dec 22, 2011 : File name as primary key?
• Oct 18, 2012 : MySQL: Unique constraint on large column

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.

Usage

In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.

Instead of

SELECT
    E.BusinessEntityID
,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
FROM
    HumanResources.Employee AS E

use a query such as

SELECT
    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
FROM
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.

Benefits

• Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
• No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
• No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...