Mysql – In MySQL if column X has unique values what’s the difference between UNIQUE index and B-Tree index

btreeindexMySQLunique-constraint

Let's assume that I have table data:

CREATE  TABLE `test`.`data` (
`row_id` INT NOT NULL ,
`data_id` INT NOT NULL ,
PRIMARY KEY (`row_id`) );

Now, let's assume that data_id has unique values. Is there any difference(space, performance, memory) between having B-Tree index on data_id and UNIQUE index on it? (besides the case when InnoDB is used as storage engine and UNIQUE index can be used as clustering key so row_id is uneccesary)

Best Answer

According to the Book

Understanding MySQL Internals

Page 198 says the following:

Paragraph 6 : A MyISAM B-tree consists of leaf and nonleaf nodes, or pages.
Paragraph 7 : Both leaf and nonleaf nodes key values and pointers to the record positions in the datafile. Nonleaf nodes additionally contains pointers to child nodes.

Given this description

a unique index would have more nonleaf nodes. This would lend itself mode towards ordering and searching.
nonunique index would requires less nonleaf nodes. This would lend itself mode towards doing range scans (tables and index)
A covering index (which would contain all needed columns for specific SELECT queries) would combine the best and worst of both. This would allow for a range scans that would need more nonleaf nodes and provide for ordering/searching. The additional benefit would be bypassing the need for reading table data if all needed columns are present and accounted for in the index.
I mentioned other aspects in my past post Benefits of BTREE in MySQL

SPACE

Unique Indexes would take up more space for nonleaf nodes.
Covering indexes would need more space than a nonunqiue index but would have a higher multi-columns index cardinality that can approach the actual row count of the table.

PERFORMANCE (Heavy-Write Environment)

SELECTs would be very fast for exact keys
SELECTs that only touch a Covering Index would be extremely fast
Random INSERTs can be a nightmare due to rotations/rebalancing of nonleaf nodes
UPDATEs that change key values can be a nightmare due to rotations/rebalancing of nonleaf nodes
The higher the cardinality, the more nonleaf nodes, the better the performance
The lower the cardinality, the fewer the nonleaf nodes, the worse the performance
The more columns in the index increases the effects of the cardinality

MEMORY

Depends on Storage Engine

MyISAM : The key buffer only caches index pages. Covering indexs are your best friend here
InnoDB : Buffer Pool Needs to be Big Enough to Hold Data Pages, Index Pages, and Index Page Changes (See InnoDB Architecture, What are the main differences between InnoDB and MyISAM?)

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

Mysql – Index use and column type

Phrases like bad for performance are essentially meaningless. Measurements are meaningful; opinions are, umm, not measurements.

But int(7) and int(11) are both 32-bit integers. The number in parens refers to display width; it has nothing to do with storage size or index size.

For varchar() columns, you can create an index that's based on the first 'n' characters. That decision has some performance implications, for example when you're searching for 'wibble' in a column indexed on the first three characters. And there are several subtle points with columns of type "text". Read CREATE TABLE carefully. (Search for "length".)

But I'd be a little surprised if you could measure any difference in joining columns of varchar(200)/varchar(200) and in joining columns of varchar(220)/varchar(200). That is, other than the obvious--storing more than 200 characters in the varchar(220) column.

There's an easy way to tell: write some code in your favorite scripting language, generate a few million rows of sample data, load it, and test it. And post your results here, too.