Use "select ... where ..." or "select ... [...]"?

Proposition: It is preferred to use "select mytable where criteria" over "select mytable[criteria]".

I only recently saw in the forum the use of a square bracket criteria expression on a select statment instead of a where clause, which I had never thought of doing before.

For example:

select mytable where name like "tim"

vs.

select mytable[name like "tim"]

Obviously, both of these produce identical results, but I wondered if there are any performance or memory management implications.

It seems like the "where ..." criteria would get applied while the select is doing its query, but the [] criteria would get applied only after the select is completed?

For example, in

select mytable[name like "tim"]

it seems like the select would return the entire table in an array, and then the criteria would be applied. For a large table with only a few records meeting the criteria, this seems at best a waste of memory management, however temporary it might be, and possibly also an unnecessary performance hit to generate the entire records array only to then reduce it by criteria.

Whereas, it seems to me that:

select mytable where name like "tim"

would not only use less memory, since the entire table is never stored into an array, and might also be faster.

I ran a test with very simple criteria on a very simple table with 25,000 records, only 50 of which meet the criteria, and it appears the brackets are a little slower than the where clause:

It would seem that there is, in fact, a slight performance hit in the simple case, but I don't have any insight into any possible memory management hits.

Does anybody know for sure how these two constructs are implemented internally and what the implications are for memory management and performance, especially in the extreme cases of many records and few hits?