How to Optimize a Query With GROUP BY and ORDER BY

I have got a POSTS table, the structure is like this:

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
  `content` longtext COLLATE utf8_turkish_ci,
  `excerpt` longtext COLLATE utf8_turkish_ci,
  `link` longtext COLLATE utf8_turkish_ci,
  `original_link` longtext COLLATE utf8_turkish_ci,
  `mime_type` longtext COLLATE utf8_turkish_ci,
  `language_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `site_id` int(11) DEFAULT NULL,
  `type` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  `is_deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `type` (`type`),
  KEY `created_at` (`created_at`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=52487 ;

And a USERS table, structed like this:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=9422 ;

I'm开发者_JS百科 using this query to get latest "page, file or post" posts ordered by descending time and grouping by user to not show all latest posts from a user:

   SELECT p.*, u.* 
     FROM posts p 
LEFT JOIN users u ON p.user_id = u.id 
    WHERE p.type IN ('post', 'page', 'file') 
 GROUP BY p.user_id 
 ORDER BY p.created_at DESC 
    LIMIT 30

But it is too slow, even limited to 30 records.

now, how can i speed up this query? which columns to index or any other ideas? thanks.

The first thing to do is to add an index on posts.user_id (or maybe posts.user_id + posts.type). And another index on posts.created_at

UPDATE
I've just payed attention that your query grabs all fields from both tables, and posts table has 6 long text columns. So I believe you have a poor performance because mysql has to create quite a large temporary table or temp file to get all rows for satisfying your group by + order by clauses. I think the following query should help.

  SELECT u.*, p1.* FROM
  users u 
  INNER JOIN 
  (
      SELECT p.user_id, p.created_at, p.id FROM posts p 
      WHERE  p.type IN ('post', 'page', 'file')  GROUP by p.user_id 
      ORDER BY p.created_at DESC LIMIT 30
  )xxx ON xxx.user_id = u.id
   INNER JOIN posts p1 ON (p1.id = xxx.id)

In terms of indices, I would suggest creating ones on posts.type (WHERE), posts.created_at (ORDER). That should help speed up the sorting.

You can try this:

SELECT p., u.
FROM (SELECT * FROM posts WHERE p.type IN ('post', 'page', 'file')) p
LEFT JOIN users u ON p.user_id = u.id
GROUP BY p.user_id ORDER BY p.created_at DESC LIMIT 30

MySQL first proccess the inner query, and with its result process the outter query with less records.

Try @Gabriel's answer, but with the LIMIT in the inner query.

SELECT p., u. FROM (SELECT * FROM posts WHERE type IN ('post', 'page', 'file') ORDER BY created_at DESC LIMIT 30) p LEFT JOIN users u ON p.user_id = u.id ORDER BY p.created_at;