0

I am coding a social network and I need a way to list the most used trends, All statuses are stored in a content field, so what it is exactly that I need to do is match hashtag mentions such as: #trend1 #trend2 #anothertrend

And sort by them, Is there a way I can do this with MySQL? Or would I have to do this only with PHP?

Thanks in advance

André Figueira
  • 6,048
  • 14
  • 48
  • 62
  • [Machine learning](http://en.wikipedia.org/wiki/Machine_learning) is not a trivial thing that can be explained in a few words. So treat it as a *real rocket science* – zerkms May 10 '12 at 11:57
  • it should be helpful enough to understand that there is no an *easy* way of doing that. To implement something even near to what you want you need to learn a lot of things – zerkms May 10 '12 at 12:01
  • It is still possible to make such thing without ML approaches. – deadrunk May 10 '12 at 12:04
  • Well yes If I was going for something as intricate as Twitter, but I just want a simple implementation of this I have a working theory how I can achieve this but want to see what others think could do so and I already have some helpful answers below... – André Figueira May 10 '12 at 12:04

4 Answers4

3

The maths behind trends are somewhat complex; machine learning may be a bit over the top, but you probably need to work through some examples.

If you go with @deadtrunk's sample code, you would miss trends that have fired up in the last half hour; if you go with @eggyal's example, you miss trends that have been going strong all day, but calmed down in the last half hour.

The classic solution to this problem is to use a derivative function (http://en.wikipedia.org/wiki/Derivative); it's worth building a sample database and experimenting with this, and making your solution flexible enough to change this over time.

Whilst you want to build something simple, your users will be used to trends, and will assume it's broken if it doesn't work the way they expect.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
2

You should probably extract the hash tags using PHP code, and then store them in your database separately from the content of the post. This way you'll be able to query them directly, rather then parsing the content every time you sort.

nairbv
  • 4,045
  • 1
  • 24
  • 26
1

  1. Create a table that associates hashtags with statuses.

  2. Select all status updates from some recent period - say, the last half hour - joined with the hashtag association table and group by hashtag.

  3. The count in each group is an indication of "trend".

eggyal
  • 122,705
  • 18
  • 212
  • 237
1

I think it is better to store tags in dedicated table and then perform queries on it. So if you have a following table layout

trend | date

You'll be able to get trends using following query:

SELECT COUNT(*), trend FROM `trends` WHERE `date` = '2012-05-10' GROUP BY trend

18  test2
7   test3
deadrunk
  • 13,861
  • 4
  • 29
  • 29