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

Question

I wonder if it makes sense to index a table containing only one column? The table will be filled with 100 or 1000 records and will be used to join another (larger table) to filter your records.

Thanks!

Answer 1

Yes and no. An explicit index probably doesn't make sense. However, defining a single column as a primary key often performed (provided that it is never NULL and unique).

This is a common practice. It is not uncommon for me to create exception tables with logic, for example:

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

The primary key index may speed up such a request.

In your case, this may not affect if in a larger table a pointer to the identifier used for the connection. However, you can give the optimizer the ability to choose which index to use.

Answer 2

My vote is that this probably doesn't make sense in your scenario. You say that this table with one column will be merged with another table to filter records in another table, so why not just delete this table, index another column in another table and filter it out?

Essentially why are you writing:

 SELECT * FROM manycols M INNER JOIN singlecol s ON m.id = s.id WHERE s.id = 123 

If so:

 SELECT * FROM manycols m WHERE m.id = 123 

Suppose the argument is that manycols has a million lines, and singlecol has a thousand. You want thousands of matching lines, this is a multi-column that would need to be indexed then for the benefit.

Suppose the argument is all lines except those specified in singlecol; you can index singlecol, but the optimizer can simply just load the entire table into a hash file, so indexing will not necessarily help

There seems to be another way to do what you need to completely remove this single column table.