Storing a URL value in MYSQL

Question

Hi everyone,

I am developing an URL bookmark application (PHP and MySQL) for myself. With this application I will store URLs in MySQL.

The question is, should I store URLs in a TEXT column or should I first parse the URL and store its components (host, path, query, fragment) in separate columns in one table? The latter one also gives me the chance of generating statistical data by grouping servers and etc. Or maybe I should store servers in a separate table and use JOIN. What do you think?

Thanks.

Answer 1

The answer depends on how you like to use this data in the future. If you like to analyze the different parts of the URL splitting them is the way to go.

If not. the INSERT, as well, as the SELECT, will be faster, if you store them in just one field.

If you know the URLs are not longer then 255 Chars, varchar(255) will be better, than text, for performance reasons.

Answer 2

I'd go with storing them in TEXT columns to start. As your application grows, you can build up the parsing and analysis functionality if you really want to. From what it sounds like, it's all just pie-in-the-sky functionality right now. Do what you need to get the basic application up and running first so that you have something to work with. You can always refactor and go from there.

Answer 3

If you seriously thing that you're going to be using it for getting interesting data, then sure, do it as a series of columns. Honestly, I'd say it'd probably just be easier to do it as a single column though.

Also, don't forget that it's easy for you to convert back and forth if you want to later. Single to multiple is just a SELECT;regex;INSERT[into another table]; multiple to single is just a INSERT SELECT with CONCAT.