Is SQL inefficient since it has to parse through everything?

Question

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?

Possible duplicate of [Do You Still Need Indexing After Database Normalization](http://programmers.stackexchange.com/questions/181730/do-you-still-need-indexing-after-database-normalization) — gnat, Jul 16 '16 at 03: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 04:08
@JustinCave I made a mistake, edited the post, also could you explain further what you mean? — korri123, Jul 16 '16 at 04:26
@gnat not really what I was asking, how did you relate this to that question? — korri123, Jul 16 '16 at 04:28
Now your query is generating a Cartesian product between the comment and article tables. Pretty sure that is not what you want-- that would be very inefficient. Either you want to join the tables and do something with the article data or you want to remove the article table from your query. — Justin Cave, Jul 16 '16 at 04:30
@JustinCave Yeah I forgot to remove that table too. So now it's correct? The original question still stands. Isn't nesting a lot more efficient than parsing through everything to see if it fits? — korri123, Jul 16 '16 at 04:51
@korri123 you really need to read up on what database indexes are. — GrandmasterB, Jul 16 '16 at 05:37

score 2 · Accepted Answer · edited May 23 '17 at 11:33

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.

Is SQL inefficient since it has to parse through everything?

1 Answers1