« first day (1382 days earlier)      last day (54 days later) » 

4:41 PM
@OlegValteriswithUkraine \o
 
cleaned up the comments under your answer
So, did I jump to the wrong conclusion?
Nice answer btw.
 
@rene thanks! I tried to clarify the idea - while I always assumed the Posts.Tags column is standalone, I did not mean that other tables are derived from it, just got too focused on the context of the question (post tags) and forgot to add the other 2 tables
@rene flattered :) I am still not sure if the post shouldn't have been split in 2 as I can see it asking 2 somewhat unrelated questions
 
Meh, it is meta. And it is hard to formulate a question when the system is blackbox
 
I should've been more careful with the wording "stored", though
@rene yeah, kind of. At least the database documentation is public
 
4:48 PM
Yeah, I imagine people will go `where post,tags like '%<java>%' and while that might lead to success you better use the other relations in that case. So that is why I prefer to at least mention that structure.
Still not sure how SEO and tags will influence btw
I don't know how SE gets scraped by google
I do know they have an awesome sitemap.xml
 
@rene well, they kind of do that even without it? :) I am guilty of forgetting about the PostTags table from time to time
@rene oh, cool, nice find
 
for the quick hacks it certainly doesn't matter much
 
@rene IIRC there supposed to be microdata sprinkled throughout pages
which often leads to odd results as there's something wrong with how Google interprets them sometimes
 
That woud be a nice challenge: make your legit post turn-up in unexpected Google search results
 
@rene would you say it's generally faster to query the Tags table for the name match, then use the id to lookup the post id in the PostTags table and then join it in with the Posts table?
 
4:56 PM
I expect that to be quicker yes. That needs an index scan of only 80,000 rows and two index seeks. Instead of a full table scan of 20,000,000 rows
 
@rene huh, yeah. I've seen mutliple reports on Google's search showing very weird results (like 0 instead of the title), but staff claims that microdata is correct
@rene hmm, yes, that makes sense - should be indeed faster
 
but SEDE runs on very decent hardware so barely notices if you have inefficient SQL
Only when you reach the 2 minute limit or have an insane join with posthistory or so it starts to really matter
 
@rene well, yeah - it becomes noticeable on queries against Stack Overflow, though, ofc, since the dataset is massive
 

« first day (1382 days earlier)      last day (54 days later) »