As you know SQL doesn't have arrays. So if you make a table "article" and wish to have comments on your articles I presume you would make another table "comment". Inside "comment" table you have the content and a foreign key named "article_id" which points to the article's id.

So to get all of the article comments you would execute this query:

SELECT comment.content 
FROM comment
WHERE ${specific article id} = comment.article_id

This is how I think you would do it.

So since the article doesn't itself have references to it's comments, the database will to iterate through every single comment posted on the website, no matter the article, to find out if the comment matches the article. So if you have thousands of articles, and each article has dozens of comments, the database has to go through tens of thousands of comments to find the few ones that match your article.
My question is isn't this really inefficient? Is there really no way to store dynamically the comment ids inside the article table?

  • 4
    Possible duplicate of Do You Still Need Indexing After Database Normalization – gnat Jul 16 '16 at 3:54
  • Your query returns every comment from every article. If so, yes, you would have to read both tables into memory and would (presumably) do a hash join between them. Normally, though, you'd be getting the comments for a single article in which case it would (presumably) use indexes on both tables. – Justin Cave Jul 16 '16 at 4:08
  • @JustinCave I made a mistake, edited the post, also could you explain further what you mean? – korri123 Jul 16 '16 at 4:26
  • @gnat not really what I was asking, how did you relate this to that question? – korri123 Jul 16 '16 at 4:28
  • 2
    @korri123 you really need to read up on what database indexes are. – GrandmasterB Jul 16 '16 at 5:37

the database has to go through tens of thousands of comments to find the few ones that match your article

No, it doesn't. This concept is called indexes. In basic terms, when you expect to do a search for something within a table, based on a specific column, you define this column as the index. Later, when you ask your database to return all items where article_id equals 123, the database doesn't have to go through all the rows of the table; instead, through the index, it determines the matches, and reads only those matches.

This is why indexes are crucial in terms of database performance. When you'll start reading about them, you'll read about numerous cases where people went from a query which took days to the same query executing within a few seconds after proper indexes were added.

So since the article doesn't itself have references to it's comments

Why not? This concept is called foreign keys. A foreign key makes it possible for you to tell that the values from this specific column of a table corresponds to the values from that column of another (or marginally the same) table.

The benefit of foreign keys is that you can't insert data which doesn't have a match within the related table. In your case, it would be impossible to have a comment with article_id set to 456 if there is no article with a primary key 456.

As you know SQL doesn't have arrays.

No, I don't. See PostgreSQL arrays.

Other databases let you store even XML (and traverse it).

Most SQL databases will also let you store nvarchar(max) which could contain a JSON of your array (or any other textual representation).

Try those alternatives, and compare their performance with a properly normalized database with proper indexes, and see by yourself which is faster to query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.