CODEWITHSUNDEEP
sqlt-sqlindexingsql-server-2012
SQL_Guy
SQL_Guy

Reputation: 353

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

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!

Upvotes: 7

Views: 5602

Answers (2)

Caius Jard
Caius Jard

Reputation: 74625

My vote is that it probably doesn't really make sense in your scenario. You're saying this table with a single column will be joined to another table to filter records in the other table, so why not just delete this table, index the other column in the other table, and filter that?

Essentially, why are you writing:

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

When that is this:

SELECT * FROM manycols m WHERE m.id = 123

Suppose the argument is that manycols has a million rows, and singlecol has a thousand. You want the thousand matching rows, it's manycols that would need to be indexed then for the benefit.

Suppose the argument is you want all rows except those in singlecol; you could index singlecol but the optimiser might choose to just load the entire table into a hash anyway, so again, indexing it wouldn't necessarily help

It feels like there's probably another way to do what you require that ditches this single column table entirely

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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.

Upvotes: 2

Related Questions