1

In Data Explorer, I would like to SELECT the same posts that would show up if I'd run a search on Stack Overflow.

For example, when I run this search on Stack Overflow, it currently shows 604,031 results. These posts I would like to analyze in Data Explorer.

What condition do I have to put in?

(Actually I was trying to find out whether "postgresql explain" is more on topic on Stack Overflow or Database Administrators, by comparing their accept rates.)

  • You can't do it in a simple way, or in any way that would be accurate. SEDE and the per-site search mechanism are two different things. – ShaWiz Aug 10 '16 at 9:09
3

You will break the Data Explorer with that query:

select  id as [Post Link]
from posts
where body like '%if%'
or body like '%loop%'

This will end in an error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Because there are no full text search indexes present in the SQL Server instance SEDE is running on. The query has to do a full scan of all rows in the Posts table. Beyond that, SEDE will never return more that 50.000 rows. SEDE is also only refreshed weekly.

If you limit the scope to a specific tag, for example and gather some stats you can get some results:

select sum(case
       when acceptedanswerid is not null
       then 1
       else 0 end) as [accepted]
       , count(*) as [all]
from posts  p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
where t.tagname = 'postgresql'
and p.body like '%explain%'

By switching the site you can run this on other databases and compare their outcome.

  • I was trying to find out whether "postgresql explain" is more on topic on SO or dba, by comparing their accept rates. – AndreKR Aug 10 '16 at 9:20
  • @AndreKR that might be answered with the query I just added – rene Aug 10 '16 at 9:31

You must log in to answer this question.

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