Datatype for URL

Question

I read that the max length of URL can be 2,000 characters. I have therefore a table with varchar(2000) column type to store URLs. But this column can not be indexing only the first 1000 characters as shown below. What is the recommended datatype for 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)

"But this column can not be indexing only the first 1000 characters" --- so? is not it enough for you? — zerkms, Nov 16 '10 at 06:05
2000 chars is not the max length of a URL, though it is well beyond the reasonable length of a URL; anything over a few hundred is at the very least not user friendly, if not downright user-hostile. Various versions of IE have limits slightly over 2000, and other browsers have different limits. — Adam Vandenberg, Nov 16 '10 at 06:08

score 3 · Accepted Answer · answered Nov 16 '10 at 12:12

Your question leaves a lot to the imagination.

For one thing we must assume your index's purpose is to serve as a primary key to avoid duplicates. You won't be developing an application that ever says to a user, "sorry, there's a mistake in your 1800-character data entry; it doesn't match, please try again."

For another thing, we must assume these URLs of yours potentially have lots of CGI parameters (?param=val&param=val&param=val) in them.

If these assumptions are true, then here's what you can do.

Make your URL column longer, as a varchar, if you need to.
Add a SHA-1 hash column to your table. SHA-1 hashes consist of strings of 40 characters (hexdigits).
Make that column your primary key.
When you put stuff into your table, use the mySQL SHA1 function to compute the hash values.
Use the INSERT ... ON DUPLICATE KEY UPDATE mySQL command to add rows to your database.

This will let you keep duplicate URLs out of your data base without confusion in a way that scales up nicely.

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

I think on duplicate he must check url in hand and stored url to check if both are same or not to tackle collision chance of hashes. — Gary Lindahl, Sep 15 '11 at 01:46
I know this is old, but this answer is MOSTLY right. Take with care. This might create collisions, avoiding you to add a new url considering it as existing when it actually wasn't. It'd be rare, but it could happen. Check also this other answer: http://stackoverflow.com/questions/6800866/how-to-store-urls-in-mysql — Francisco Presencia, Nov 18 '12 at 19:50

score 1 · Answer 2 · edited Nov 28 '12 at 12:03

How about

alter table myweb create FULLTEXT INDEX on myweb_idx1(url);

Although I have to agree with zerkms that a 1000 char index should be more than enough, considering the fact that you are very unlikely to encounter a url longer than that, and even then the 1000 char prefix should do a fine job.

Regarding your original question: I think it's safe to save URLs in varchars. Where are these urls coming from ? Who's the producer of the data? You can probably enforce limits.

If you're crawling the web for urls, then you are almost certainly not going to happen upon a 2000 char url , cause the only way I can imagine getting there would be with GET data.

Hope this rambling makes sense.

Datatype for URL

2 Answers2

Linked