StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
10239593
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-04-20T02:50:43.833
FavoriteCount
0
LastActivityDate
2012-04-20T02:50:43.833
LastEditDate
LastEditorUserId
0
OwnerUserId
624884
ParentId
10223164
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
So after thinking about this for a while, I decided that the relevance ranking has to be done with 1 query within MySQL. This is because: <ul> <li>Relevance between seperate queries can't be compared.</li> <li>It's hard to combine the contents of multiple searches together in meaningful ways.</li> </ul> I have switched to using 1 index table dedicated to search. Entries are inserted, removed, and updates depending on inserts, removals and updates to the real underlying data in the innodb tables (this is all automatic). The table looks like this: <pre><code>search ============== id //id for the entry type //the table the data came from column //column the data came from type_id //id of the row the in the original table content //text </code></pre> There's a full text index on the content column. It is important to realize that not all columns from all tables will be indexed, only things that I deem to be useful in search has been added. Thus, it's just a simple case of running a query to match on <code>content</code>, retrieve what we have and do further processing. To process the final result, a few more queries would be required to ask the parent table for the title of the search result and perhaps some other meta data, but this is a workable solution. I don't think this approach will really scale (updates and inserts will need to update this table as well), but I think it is a pretty good way to provide decent application wide search for smaller deployments of the application. For scalability, use something like elastic search, solr or lucene.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POAggregate most relevant results with MySQL's fulltext search across many tables
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USF21
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POAggregate most relevant results with MySQL's fulltext search across many tables
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.