Leaderboard

TABLES CREATE TABLE `links` ( +---------+-------------------------------+----------------------------------------------------+ `link_id` int(11) NOT NULL AUTO_INCREMENT, | link_id | url | title | `url` varchar(45) DEFAULT NULL, +---------+-------------------------------+----------------------------------------------------+ `title` varchar(100) DEFAULT NULL, | 1 | https://ektoplazm.com/explore | Ektoplazm - Free music portal and psytrance netlab | PRIMARY KEY (`link_id`), | 2 | http://www.google.com | Google | FULLTEXT KEY `idx_links_url` (`url`,`title`) | 3 | http://yahoo.com | Yahoo | ) ENGINE=InnoDB; +---------+-------------------------------+----------------------------------------------------+ CREATE TABLE `terms` ( +---------+---------+-----------+ `term_id` int(11) NOT NULL AUTO_INCREMENT, | term_id | link_id | term | `link_id` int(11) DEFAULT NULL, +---------+---------+-----------+ `term` varchar(45) DEFAULT NULL, | 1 | 1 | radio | PRIMARY KEY (`term_id`), | 2 | 1 | music | FULLTEXT KEY `idx_terms_term` (`term`) | 3 | 1 | psytrance | ) ENGINE=InnoDB; | 4 | 1 | trance | | 5 | 1 | goa | | 6 | 2 | search | | 7 | 3 | search | | 8 | 3 | engine | | 9 | 3 | yahoo | +---------+---------+-----------+ FULLTEXT SEARCH mysql> SELECT l.link_id -> , l.url -> , l.title -> , t.term -> , SUM(MATCH(t.term) AGAINST('search music' IN BOOLEAN MODE) + MATCH(url, title) AGAINST("yahoo")) as `rank` -> FROM links l -> JOIN terms t ON l.link_id = t.link_id -> WHERE MATCH(t.term) AGAINST('search music' IN BOOLEAN MODE) -> OR MATCH(url, title) AGAINST("yahoo") -> GROUP BY title -> ORDER BY `rank` DESC LIMIT 200; +---------+-------------------------------+----------------------------------------------------+--------+--------------------+ | link_id | url | title | term | rank | +---------+-------------------------------+----------------------------------------------------+--------+--------------------+ | 3 | http://yahoo.com | Yahoo | search | 1.7925547659397125 | | 1 | https://ektoplazm.com/explore | Ektoplazm - Free music portal and psytrance netlab | music | 0.9105787873268127 | | 2 | http://www.google.com | Google | search | 0.4266865849494934 | +---------+-------------------------------+----------------------------------------------------+--------+--------------------+