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` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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.
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
edit:
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