On 28-Mar-2003 Aaron Williams wrote:
> I'd like to know about any potential issues of choosing a char(32) as
> the column type of a primary key instead of BIGINT or some other numeric
> type. A little background.
>
> I would like to generate a unique identifier to use as a primary key for
> our application instead of using an AUTOINCREMENT column. This is to
> give us database independence. I've seen several algorithms that will
> generate a GUID based on timestamp, machine IP, etc and return a 32 byte
> string that is guaranteed unique. However, there are concerns that
> joins using this key versus a large integer would cause performance
> problems as the table grows.
>
> Would joins of tables with character based primary keys be slower than
> those with numeric based keys? Has anyone had experience implementing a
> character-based primary key in a table of non-trivial size (> 500,000
> rows)? Thanks for any assistance or pointers.
>
Hi Aarn,
I've been encoding urls with:
$qry="REPLACE DELAYED INTO url (id, uri)
VALUES(conv(PASSWORD('$HTTP_REFERER'),16,10), '$HTTP_REFERER')";
the url table layout is:
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| id | bigint(20) unsigned | | PRI | 0 | |
| hittime | timestamp(14) | YES | | NULL | |
| uri | varchar(255) | YES | | | |
+---------+----------------------+------+-----+---------+-------+
The MySQL PASSWORD() function is pretty neat. It'll give a nice
16 digit hex hash for any string. And the distribution is much better (in
my experience) than md5().
The conv() bit will convert it to bigint and makes it very fast to index.
mysql> select count(*) from url;
+----------+
| count(*) |
+----------+
| 346923 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from hist_hits;
+----------+
| count(*) |
+----------+
| 4954220 |
+----------+
1 row in set (0.00 sec)
This works quite well on a site with > 2 million hits/day. --A 'hit'
being 1 select and 2 or more inserts.
HTH.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
