0

I'm not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I'm questioning this for a long time.

Assume there are 2 tables, one table with a lot of records, and one table is just static data with 2 records (for example: a Yes/No table).

As example take these 2 tables:

  • Table_Yes_No (only 2 records: Yes and No. Of course they have an incrementing clustered PK)
  • Table_Form (Very big table with lots of records, and one column has FK to the PK of Table_Yes_No).

Now my question; Is it worth putting an index on the FK (reference from Table_Form to the Table_Yes_No table)? The column will only contain 1 or 2 (but not sorted, because no index). Is it worth indexing such a FK?

An example in SQLFiddle: http://sqlfiddle.com/#!18/51614/3/0

That column will be queried anyway, the question is will the index help the performance or not. The small table is static data, will never be changed. The big table will be queried heavily, and there will be also a lot of CRUDs on it.

5

Short answer

No.

Long answer

No, and adding the index could be detrimental to performance.

Something that factors heavily into whether or not a secondary/non-clustered index is even used is how selective it is (and the searches you are trying to perform are). Y/N has two values - it's ability to be selective is going to depend on the proportion of Y to N. If they are evenly split, and there is no rhyme/reason to Y/N with relation to the clustered index, they will not be selective and the query optimizer will almost always ignore that index.

The potential slowdown occurs if you issue a delete on the larger table or an update against that Y/N column. There's no efficient way to update the secondary index as it is organized by Y/N - so the only option is to basically scan the entire index looking for the record to update/delete. Depending on the number of rows in the main table and the size of the clustered index, this could be an issue.

Caveats

If there are few Ys or few Ns, and you need to locate those records quickly, you can use a filtered index to create a very small index that contains only records for the sparsely populated value. This can be beneficial and has a much lower footprint than a traditional non-clustered index would in that situation.

If you're just trying to enforce Y/N (or some other easily understood code set), that can be handled easily through a check constraint, which will have a lower overhead than the FK.

ADD CONSTRAINT CK_<ColumnName>_Is_YesNo CHECK (<ColumnName> IN ('Y','N'))

9
  • Don't focus on the YesNo table, it could be anything. Point I wanted to make is that it's a table with static data with very few records in it. So can I conclude that adding an index in this scenario will mostly end up in not improving the performance and likely it will even cause poor performance? – Ozkan Feb 25 at 16:14
  • 2
    @Ozkan - Yes, the issue isn't Y/N, it's the cardinality and how it impacts the ability of the index to be used efficiently. – bbaird Feb 25 at 16:34
  • from how many records in the static table would you consider is enough cardinality to create an index? – Ozkan Feb 25 at 17:59
  • @Ozkan Cardinality isn't based on the total number of records in the table, rather it's based on the number of records a value for the index would return (the uniqueness) when used in a predicate. In other words in your YesNo table, using bbaird's example where there's an equal number of Yes values to No values is an example where the cardinality is the same for either value. But if you had 100 Yes values and 1 No value then the cardinality for Yes values would be magnitudes higher then No values, and therefore a filtered index on No would make a good index because of its low cardinality. – J.D. Feb 25 at 19:03
  • Low cardinality = high uniqueness. So without knowing the actual content of your data it's not possible to calculate the answer to your question, and in general isn't a super easy calculation to make. E.g. if your field had 5 or 10 different possible values, then the ratios of uniqueness vary compared to a table with only 2 possible values. – J.D. Feb 25 at 19:06
1

Placing the foreign key will cause internally a key lookup into the one page that will be required by your Yes/No table. This will be trivial for the most part and will not consume much cpu, but the code path of a simple check constraint will likely be faster and wont involve any locks or latches.

For the scenario you have where the values of the column is basically a boolean value, you should really use just a check constraint.

Also, I don't see much reason for the Yes/No table to exist if it is essentially a primitive.

2
  • The tables are made up just to make my point, these table don't exist :) And can you explain in plain English please. I don't understand what you are saying... code path, check constraint, primitive, ... all unkown for me – Ozkan Feb 25 at 16:08
  • 1
    Using the foreign key to a table instead of a check constraint (docs.microsoft.com/en-us/sql/relational-databases/tables/…) will be more expensive in terms of processing (cpu, synchronization objects) for your example. If you have a small static set of values that won't change for a column, use a check constraint as best practice. – Fernando Sibaja Feb 25 at 16:43
1

Indexes on foreign keys are prescriptive. IE they are not there to optimize SELECT queries, they are there to prevent excessive locking and scanning when updating the primary key table.

So an exception to the general rule is fine for foregn keys to tables which are essentially static (ie updated, if at all, during maintence windows), supporting the index with a foreign key is optional. And indexes on such a foreign key column would follow the normal rules for any non-clustered index, and would depend on the usefulness of the index for the workload.

Note that an index on a low-cardinality fk might be very useful. Eg

select count(*) from T where SomeFlag='Y'

would benefit greatly from an index on SomeFlag.

2
  • Not the count but a normal select * from T where SomeFlag=1. (1 = PK for 'Yes' in the static table). Would an index help here on T.SomeFlag? The YesNo table is illustrative, please consider it as an example. – Ozkan Feb 26 at 7:55
  • Only if a low percentage of rows have SomeFlag=1. – David Browne - Microsoft Feb 26 at 13:12
0

If you will delete (or update the id) records from small table index helps.

If you dont index big table delete will lock and scan the whole table.

If there is an index delete statement will also lock the table but scan the index which is cheaper.

2
  • The small table is static data, will never be changed. The big table will be queried heavily, and there will be also a lot of CRUDs on it. – Ozkan Feb 25 at 15:52
  • If it is static you dont need to create index on the big table. – Burak Yurdakul Feb 26 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.