(There's no foreign key relation between the two)

I type sdk in Tags field in stackoverflow.
It shows iphone because iphone-sdk is a synonym for iphone (and SO obviously sort tag list by used-count)

Isn't having a foreign key to Tags instead of TargetTagName better than the shown schema to do the job(return the sorted tag-list)?


I don't know if having a foreign-key on id instead of TargetTagName is better, for that you have to define better.

The tagsynonyms table isn't particularly big. On Stack Overflow it counts 3,209 rows the moment, so a full-table scan wouldn't be much of a problem, in the context of The Data Explore that is.
The tags table hold 42,533 rows.

We can only guess how those queries for tag search are run (I doubt it is even run, just fetched from memory but let's skip that) so maybe this guess will work:

select *
from (
  select id
       , tagname
       , [count]
       , tagname as tname
  from tags 
  select t.id
       , tagname
       , [count]
       , sourceTagName
  from tags t
  inner join tagsynonyms ts 
          on ts.targettagname = t.tagname
  ) ctags
-- where tname like '%sdk%'
order by [count] desc

In its first run it fetched 45,760 rows in 101 ms and this is the execution plan that was used:

execution plan of tags union with tagsynonyms

If you uncomment the where clause the following index comes into play:


cutting down the results to 165 rows and 45 ms.

I don't see a specific use case where the current schema would benefit from such foreign key design. Only if you have an information requirement that can't be achieved due to the current technical implementation there would be reason to change or add foreign-keys and possible indexes.

tl;dr; I don't think having a foreign-key on id's instead of tagname in this context add much value. The current design in SEDE is fine for its purpose.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .