Question

I've been playing around with some large (for me) tables -- 50k to 100k rows, and I've noticed that simple selects using a single unindexed column , for example:

SELECT name FROM names where name = "Mary";

Return in ~ 1/100 of a second from the command line. I had assumed this sort of query would be slow, but its not.

Is there a point where this type of select without an index would be noticeably slower?

Answer 1

There is no simple answer to you question, but in most cases: very soon ;)

Before going into a little bit more detail, we need to clarify some terms:

fast and slow
I like to avoid these terms entirely, because they are hard to defined.
faster and slower
These are easy, if a query needs less time to complete with index than without, it is faster with index :)
fast enough and too slow
These terms assume a particular purpose. Every query has a purpose. It can be fast enough for that purpose, or not (too slow).

Your sentence about 1/100 sec, and "not slow" is therefore hard to comment on. It seems that 1/100 sec is fast enough for your purpose. I'm usually measuring query time in milliseconds (1/1000). Simple queries (e.g. PK lookup) will usually take < 1ms on the database side. Form that perspective, it is slower than the usual "simple" query I work with.

I suppose your query would be faster with an index, because 50k records is almost certainly enough so that the index makes sense. You might need a more sophisticated tool for measurement, to see the difference. From your question, I'd say there is a huge difference already, you just don't notice.

However, even if the query is fast enough without index at the moment, you should not forget about the future. How will it perform on 10 times as many rows, and how will it perform if the database host is under load? Chapter 3, demonstrates these effects. Read at least the "Data Volume" section.

I think it is easier to answer your question the other way around:

Q: "When is a full table scan more effective than an index lookup?"
A: When a large fraction of the table is read. "Large" depends, of course, and can be as low as 1% of the table.