Does it make sense to index a table with just one column?

Question

I wonder if it makes sense to index a table, which contains just a single column? The table will be populated with 100's or 1000's of records and will be used to JOIN to another (larger table) in order to filter its records.

Thank you!

Answer 1

Yes and no. An explicit index probably does not make sense. However, defining the single column as a primary key is often done (assuming it is never NULL and unique).

This is actually a common practice. It is not uncommon for me to create exclusion tables, with logic such as:

from . . . 
where not exists (select 1 from exclusion_table et where et.id = ?.id)

A primary key index can speed up such a query.

In your case, it might not make a difference if the larger table has an index on the id used for the join. However, you can give the optimizer of option of choosing which index to use.