Re: Storing and fast retrieving of Email address
What's the advantage of partitioning? The engine will still have to drill down a BTree to locate the desired page. No reduction in "seek time".
What's the advantage of changing from a varchar to an int? Well, one answer is if you have lots of other tables that use email address as a key. In that case, you are cluttering disk with copies of long keys, when you could have just a shorter key.
You question of WhatFunction(email) -- suggest MD5(). But you may as well do it in your application rather than shipping the email to/from Mysql.
The odds of a dup in md5 (or sha1) are sufficiently minuscule that it can be ignored.
Alternatively, you can use a lookup table to map between email and id:
CREATE TABLE email_map (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(...) NOT NULL,
PRIMARY KEY (id),
UNIQUE (email)
) ENGINE=InnoDB;
You could then use this via JOIN or subquery to do the necessary lookups coming and going.
Caveat: The mapping table works only if the string (email) is fits in the restriction (1K?) of how big an index can be. If you exceed that (say for images), the mapping table and its API becomes much more complicated. I have several techniques, none pretty.
Subject
Views
Written By
Posted
11527
March 17, 2008 04:42PM
4158
March 20, 2008 08:21AM
3286
March 25, 2008 05:54PM
2919
March 25, 2008 07:34PM
Re: Storing and fast retrieving of Email address
2728
May 29, 2008 09:53AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.