Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates



(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)?

share|improve this question
possible duplicate of Information about database indexes in the Data Explorer – Paul Sweatte Sep 4 '15 at 18:46

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 
       , 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.

share|improve this answer

You must log in to answer this question.

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