AEGVrqtY

1How to Optimize a Query With GROUP BY and ORDER BY
2CREATE TABLE IF NOT EXISTS `posts` (
3  `id` int(11) NOT NULL AUTO_INCREMENT,
4  `title` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
5  `content` longtext COLLATE utf8_turkish_ci,
6  `excerpt` longtext COLLATE utf8_turkish_ci,
7  `link` longtext COLLATE utf8_turkish_ci,
8  `original_link` longtext COLLATE utf8_turkish_ci,
9  `mime_type` longtext COLLATE utf8_turkish_ci,
10  `language_id` int(11) DEFAULT NULL,
11  `user_id` int(11) DEFAULT NULL,
12  `site_id` int(11) DEFAULT NULL,
13  `type` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
14  `created_at` datetime DEFAULT NULL,
15  `modified_at` datetime DEFAULT NULL,
16  `is_deleted` int(11) DEFAULT NULL,
17  PRIMARY KEY (`id`),
18  KEY `user_id` (`user_id`),
19  KEY `type` (`type`),
20  KEY `created_at` (`created_at`),
21) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=52487 ;
22	
23CREATE TABLE IF NOT EXISTS `users` (
24  `id` int(11) NOT NULL AUTO_INCREMENT,
25  `username` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
26  PRIMARY KEY (`id`),
27  KEY `username` (`username`)
28) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=9422 ;
29	
30SELECT p.*, u.* 
31     FROM posts p 
32LEFT JOIN users u ON p.user_id = u.id 
33    WHERE p.type IN ('post', 'page', 'file') 
34 GROUP BY p.user_id 
35 ORDER BY p.created_at DESC 
36    LIMIT 30
37	
38SELECT u.*, p1.* FROM
39  users u 
40  INNER JOIN 
41  (
42      SELECT p.user_id, p.created_at, p.id FROM posts p 
43      WHERE  p.type IN ('post', 'page', 'file')  GROUP by p.user_id 
44      ORDER BY p.created_at DESC LIMIT 30
45  )xxx ON xxx.user_id = u.id
46   INNER JOIN posts p1 ON (p1.id = xxx.id)