mysql - Datatype for URL -
i read max length of url can 2,000 characters. have therefore table varchar(2000) column type store urls. column can not indexing first 1000 characters shown below. recommended datatype url?
mysql> create table myweb(id int not null auto_increment, url varchar(2000), primary key (id)); query ok, 0 rows affected (0.03 sec) mysql> alter table myweb add key (url); query ok, 1 row affected, 1 warning (0.04 sec) records: 1 duplicates: 0 warnings: 0 mysql> show create table myweb\g *************************** 1. row *************************** table: myweb create table: create table `myweb` ( `id` int(11) not null auto_increment, `url` varchar(2000) default null, primary key (`id`), key `url` (`url`(1000)) ) engine=myisam auto_increment=2 default charset=latin1 1 row in set (0.00 sec)
your question leaves lot imagination.
for 1 thing must assume index's purpose serve primary key avoid duplicates. won't developing application ever says user, "sorry, there's mistake in 1800-character data entry; doesn't match, please try again."
for thing, must assume these urls of yours potentially have lots of cgi parameters (?param=val¶m=val¶m=val) in them.
if these assumptions true, here's can do.
make url column longer, varchar, if need to.
add sha-1 hash column table. sha-1 hashes consist of strings of 40 characters (hexdigits).
make column primary key.
when put stuff table, use mysql sha1 function compute hash values.
use insert ... on duplicate key update mysql command add rows database.
this let keep duplicate urls out of data base without confusion in way scales nicely.
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
Comments
Post a Comment