Beautifying URLs with FULLTEXT index

Question

I am currently building a website with multiple pages and in order to beautify the site's URLs I am using addresses like http://mydomain.com/category/item-name

I am using MySQL tables so in order to fetch the current item from my MySQL I have two options:

1) Add the item's ID to the title: http://mydomain.com/category/28745/item-name (where 28745 is the ID in the table). That way I can run a query SELECT * FROM products WHERE ID=28745 . Easy approach but the problem is that the URL is a bit uglier.

2) Fetch the item using a text search. In that case I will use the item-name as a FULLTEXT (using MyISAM) so the query will be SELECT * FROM products WHERE item-name=some-text .

I am trying to find out if there are any downsides to the second approach. Does using FULLTEXT instead of an Index on an INT field cost in performance? Does it really matter to search engines if the URL consists of the ID and is a bit uglier?

Thanks,

Meir

Answer 1

You don't need a FULLTEXT index, that's the first thing. A FULLTEXT index is an index used for searching of the database of text. What you're doing is exact matching, you're not searching for entries.

That said, what's the downside of having an index over textual column over integer one?

First thing is the size. Integers require less storage space. Their indexes require less storage space. In order to store an integer, you need 4 bytes (2^32 is the range). To store a single ASCII char you need 1 byte. So, a word that's containing over 4 letters will take up more space than number 4.5 billion.

Second thing is that you're forced to use MyISAM if you want to have fulltext indexes for some reason. There are advantages and disadvantages of MyISAM over InnoDB and that's a topic well-covered here at SO.

In short - unless you have 100k+ categories and growing and unless you need advanced searching options for your categories - don't use a fulltext index, use the regular one. Table engine is up to you to decide. For small amount of data it will all work without any issue.