# hash index lookup

**URL:** <https://forums.percona.com/t/hash-index-lookup/3364>\
**Category:** Other MySQL® Questions\
**Created:** [March 26, 2014, 2:44am UTC](https://forums.percona.com/t/hash-index-lookup/3364 "2014-03-26T02:44:42Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![sinterklaas](https://avatars.discourse-cdn.com/v4/letter/s/7cd45c/32.png) [@sinterklaas](https://forums.percona.com/u/sinterklaas)\
**Post date:** [March 26, 2014, 2:44am UTC](https://forums.percona.com/t/hash-index-lookup/3364/1 "2014-03-26T02:44:42Z")

</div>

Hi,  
After asking this question to the publishers, they suggested I post it here, so here it goes:

I tried the hash indexing techniques mentioned in Chapter 5 (Indexing for high performance) of “High Performance MySQL” by Schwartz, Zaitsev and Tkachenko.  
Below is a description of what I tried, my setup and my results. I hope you can comment on that and let me know if I missed anything, misunderstood anything or did anything wrong.

I have a table of (several millions of) urls which is growing a bit large (a few GiB) and I figured using a hash index on the urls might decrease this size.  
This is the original create code of my table (without hash index):  
CREATE TABLE `urls` (  
`Id` INT(10) UNSIGNED NOT NULL AUTO\_INCREMENT, `URL` TEXT NOT NULL, PRIMARY KEY (`Id`), UNIQUE INDEX `Url` (`URL`(255)) USING BTREE  
)  
COLLATE=‘utf8\_general\_ci’  
ENGINE=InnoDB

For a table with 1 million urls in it (I experimented with just a subset of my complete urls table), this results in a table of about 240 MiB.

Using a hash index on the url column, as all suggested and explained in Chapter 5:

CREATE TABLE `pseudohash` (

`id` INT(10) UNSIGNED NOT NULL AUTO\_INCREMENT,

`url` VARCHAR(255) NOT NULL,

`url_crc` INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

PRIMARY KEY (`id`),

INDEX `UrlHash` (`url_crc`)

)

COLLATE=‘utf8\_general\_ci’

ENGINE=InnoDB

I managed to basically half the size of the table. The same 1 million urls in this table result in a table of about 120 MiB.  
For completeness’ sake, I copy the trigger I used before inserting into this table (but it’s basically directly taken from Chapter 5 as well):  
SET @OLDTMP\_SQL\_MODE=@@SQL\_MODE, SQL\_MODE=‘STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION’;  
DELIMITER //  
CREATE TRIGGER `pseudohash_crc_ins` BEFORE INSERT ON `pseudohash` FOR EACH ROW BEGIN SET NEW.url\_crc=crc32(NEW.url); END// DELIMITER ; SET SQL\_MODE=@OLDTMP\_SQL\_MODE;

So I was very happy about significantly reducing storage space for this table. But then I decided to compare lookup times for both tables.  
I took the first 10.000 rows from both tables (the content of both tables are identical, apart from the url\_crc column) and measured the time it took to look them up one by one, using a simple ‘SELECT id FROM urls WHERE url=’$url’'. Obviously, because in the second case, there is no index on url, I needed an extra clause, adding up to: ‘SELECT id FROM pseudohash WHERE url\_crc=CRC32(($url)) AND url=($url)’.

Doing this in a script on my system (not really relevant, since it’s the relative difference I’m interested in, but in any case: Windows 8.1 64-bit, 3.4 GhZ processor, 12 GB memory) resulted in the following:  
Looking up these 10k urls in the table with normal/b-tree index: roughly 17 seconds.  
Looking up the same 10k urls in the table with hash index: roughly 1 minute and 18 seconds.

Though significantly reducing storage space, this performance issue renders this implementation of hash index pretty much useless in my case. So my question is:  
Is this extra time just due to the fact that this extra operation has to be done (‘WHERE url\_crc=CRC32($url)…’) and is this a known drawback of this hash index approach? Or am I missing something or did I do anything stupid to unnecessarily slow it down?

Hope you can answer my question. If there is any additional information you need in order to be able to answer my question, I would be happy to provide you with that. Please let me know!

cheers,  
peter