0

On my website I've build myself I have the links to the articles looking as follows:

my_website.com/article/33/some-article
my_website.com/article/213/another-article

Say there're around 10 000 of them. Now they're retrieved by an id only, the part that goes after an id is added to an url on the fly when an article has been retrieved already. I want to change them to look like this:

my_website.com/article/some-article
my_website.com/article/another-article

Thus I'll need to add an index to "article_friendly_title". It might be 50 characters long. I wonder, will that bring a lot of overhead and about how much will it slow down the fetching from a db articles process? My guess it'll be significantly slower. Nonetheless, there're many websites that have the same kind of url for products or articles and they seem to be fine with that.

Incerteza
  • 32,326
  • 47
  • 154
  • 261

1 Answers1

0

Most database implementations use a binary tree for index columns, which means that the indexed column is searchable in O(log(n)) time. At worst, the algorithm will find whether a search term exists in the database with 10,000 rows in 14 comparisons.

If you're familiar with binary search, or have ever written an algorithm, it simply invokes a greater than, less than, or equals comparison.

The datatype of the indexed column will make very little difference, since evaluating greater than, less than, or equals on a string of fixed length (even 50 characters) is an operation considered O(1).

Sources: http://www.programmerinterview.com/index.php/database-sql/what-is-an-index/ https://www.cs.cmu.edu/~adamchik/15-121/lectures/Trees/trees.html

One other consideration, if you haven't thought of it already, would be to ensure unique names for your "friendly article name" column.

Nate Vaughan
  • 3,471
  • 4
  • 29
  • 47
  • if I have an index on "friendly article name" column, a db insures its unqueness, doesn't it? – Incerteza Aug 25 '16 at 02:47
  • and the question is that, an article would have 50 characters long "friendly article name" column, thus an index on it would be -- how long? at least 50 bytes, or maybe 100 bytes. as opposed to an integer index of 4 bytes long. won't it be a great overhead? – Incerteza Aug 25 '16 at 02:49
  • 1
    In pure theory, the length of the string being compared is still considered *O(1)* since the string length doesn't change as the size of the database scales. In practical terms, this could make a difference when comparing very long, very similar strings. But remember that comparing the strings "A look at the new Batman" and "Why I'm skipping Transformers 6" only requires a 1 byte of comparison ('W' > 'A'). Also remember that even if you had to compare all 50 bytes of two strings 14 times, this is 700 comparisons, which will likely be indistinguishable performance than comparing integers. – Nate Vaughan Aug 25 '16 at 02:58
  • 1
    Strictly speaking it is usually not binary tree, but rather B-tree or B+tree. – Antonín Lejsek Aug 25 '16 at 03:04
  • I didn't mean compasion time and cmplexity but the space an index of "friendly article name" will take in a db. it's at least 10-20 time more, isn't it? – Incerteza Aug 25 '16 at 03:07
  • Yes absolutely it will take more space in the db: approximately 490kb more for your database of 10K rows. – Nate Vaughan Aug 25 '16 at 11:56
  • Not much, actually, very little. Therefore, in general there'll be no major difference in performance and overhead if I replace "id" with "friendly url", correct? – Incerteza Aug 25 '16 at 14:18
  • In my opinion, the question of "how much size will it add" and "how will it perform" are two different questions. – Nate Vaughan Aug 25 '16 at 14:29