YtDYePGM

1CREATE TABLE IF NOT EXISTS `tgmp_games` (
2  `g_id` int(8) NOT NULL AUTO_INCREMENT,
3  `site_id` int(6) NOT NULL,
4  `g_name` varchar(255) NOT NULL,
5  `g_link` varchar(255) NOT NULL,
6  `g_url` varchar(255) NOT NULL,
7  `g_platforms` varchar(128) NOT NULL,
8  `g_added` datetime NOT NULL,
9  `g_cover` varchar(255) NOT NULL,
10  `g_impressions` int(8) NOT NULL,
11  PRIMARY KEY (`g_id`),
12  KEY `g_platforms` (`g_platforms`),
13  KEY `site_id` (`site_id`),
14  KEY `g_link` (`g_link`),
15  KEY `g_release` (`g_release`),
16  KEY `g_genre` (`g_genre`),
17  KEY `g_name` (`g_name`),
18  KEY `g_impressions` (`g_impressions`)
19) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
20	
21CREATE TABLE IF NOT EXISTS `tgmp_reviews` (
22  `r_id` int(8) NOT NULL AUTO_INCREMENT,
23  `site_id` int(6) NOT NULL,
24  `r_source` varchar(128) NOT NULL,
25  `r_date` date NOT NULL,
26  `r_score` int(3) NOT NULL,
27  `r_copy` text NOT NULL,
28  `r_link` text NOT NULL,
29  `r_int_link` text NOT NULL,
30  `r_parent` int(8) NOT NULL,
31  `r_platform` varchar(12) NOT NULL,
32  `r_impressions` int(8) NOT NULL,
33  PRIMARY KEY (`r_id`),
34  KEY `site_id` (`site_id`),
35  KEY `r_parent` (`r_parent`),
36  KEY `r_platform` (`r_platform`)
37) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;
38	
39SELECT * FROM tgmp_games g
40RIGHT JOIN tgmp_reviews r ON g_id = r.r_parent
41WHERE g.site_id = '34'
42GROUP BY g_name
43ORDER BY g_impressions DESC LIMIT 15
44	
45id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
461   SIMPLE  r   ALL     r_parent    NULL    NULL    NULL    201133  Using temporary; Using filesort
471   SIMPLE  g   eq_ref  PRIMARY,site_id     PRIMARY     4   engine_comp.r.r_parent  1   Using where
48	
49SELECT g_id
50  FROM tgmp_games g
51 WHERE site_id = 34
52 ORDER BY g_impressions DESC
53 LIMIT 15;
54	
55SELECT r_parent, max(r_score)
56  FROM tgmp_reviews r
57 WHERE r_parent IN (/*1st query*/)
58 GROUP BY r_parent;