Take the 2-minute tour ×

I'm trying to implement hashtag suggestion while a user is typing, So I'm building an hashtag table like this:

CREATE TABLE IF NOT EXISTS `hashtags_weight` (
  `hashtag` varchar(140) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `tot` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `hashtag` (`hashtag`,`tot`)

which I'll later query with:

SELECT hashtag  FROM hashtags_weight WHERE hashtag LIKE 'foob%' ORDER BY tot DESC

the source I'm getting the hashtags from is from previously inserted hashtags. Now, while this is all pretty basic stuff I thought it would be preferrable to suggest an hashtag with the most used case. suppose I have a table with:

10 Foobar
5 FooBar
2 foobar

this means that 10 times Foobar has been used, 5 FooBar and 2 foobar. now, I'd like to count as 17 the hashtag and propose only Foobar during the autocompletion.

Of course I can do it with some array sorting but I already see that it's taking a lot of time (there are a lot of rows). I'd like to hear some different approach to the matter.

p.s. this job will run nightly, so there's no need for it to be super fast. I just want to make sure I'm doing it properly. of course I could add more table to the Db, no problem with that.

share|improve this question

1 Answer 1

Update your query:

SELECT count(*) as count,hashtag  FROM hashtags_weight WHERE hashtag LIKE 'foob%' GROUP BY hashtag ORDER BY tot DESC

Add a count & a group by and it should work since your table is case insensitive already.

Not sure how you got to the 10,5 & 2 already buy I assume you did this with PHP


I see you use 'tot'. Does this hold the total? In that case:

SELECT hashtag,sum(tot) as total FROM hashtags_weight WHERE hashtag LIKE 'foob%' GROUP BY hashtag ORDER BY tot DESC
share|improve this answer
I'm not doing this where users are adding their hashtags. that is another table. this table is just meant for lookup (it's already a 'view' on its own). so I need to sum 10,5,2 and save the data on hashtags_weight as Foobar, 17 –  sathia Aug 16 '12 at 9:42

Your Answer


