GROUP BY with ORDER BY extremely slow on database (seems to create temp table)

Question

I'm building a twitter app that displays posted links on twitter, but I have a problem when sorting the table by time.

tweet
+----------------------------------------+
| tweet_id | [...] | created_at          |
+----------------------------------------+  
| 123456   | [...] | 2012-06-11 11:31:28 |
| 234567   | [...] | 2012-06-11 11:32:55 |
| 345678   | [...] | 2012-06-11 11:33:22 |
+----------------------------------------+

tweets_url
+---------------------+
| tweet_id | url      |
+---------------------+
| 123456   | cnn.com  |
| 123456   | fox.com  |
| 234567   | abc.com  |
| 345678   | abc.com  |
+---------------------+

Heres my SQL (I'm using GROUP by to return only unique URLS):

SELECT tweet_urls.url,
    FROM  `tweets` 
    LEFT JOIN tweet_urls ON tweet_urls.tweet_id = tweets.tweet_id 
    WHERE tweet_urls.url LIKE '%cnn.com%'
    GROUP BY tweet_urls.url 
    ORDER BY tweets.created_at DESC LIMIT 0 , 20

I tried different variations of running this query with outer select from here, using different joins and inner SELECTS.

Edit: I've done some further testing. It seems that Mysql creates a temporary table based on the GROUP BY tweet_urls.url and then orders the results w/o using the specified index because it is run on a temp table.

Here's the EXPLAIN output:

+----+-------------+------------+--------+---------------+---------+---------+-----+----------------------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref |                rows  | Extra                                        |
+---------------------------------------------------------------------------------------------------------+----------------------------------------------+
| 1  | SIMPLE      | tweet_urls | index  | tweet_id      | url     | 422     | NULL                 86783 | Using where; Using temporary; Using filesort 
| 1  | SIMPLE      | tweets     | eq_ref | PRIMARY       | PRIMARY | 8       | tweet_urls.tweet_id        |
+----+-------------+------------+--------+---------------+---------+---------+-----+----------------------+----------------------------------------------+

Answer 1

I think the real problem is here:

WHERE tweet_urls.url LIKE '%cnn.com%'

This type of query (LIKE without a constant prefix) cannot efficiently use an index.

You can solve this problem by adding an extra column to your table, called domain and indexing that. You can then change your query to:

WHERE tweet_urls.domain = 'cnn.com'

Answer 2

Put an index on the tweets.created_at column

Answer 3

Before you tune the statement, please be sure that the statement is guaranteed to return a correct result set, the result set you are expecting. (see below)

As for performance, the LIKE '%foo' predicate (with the leading wildcard) is not sargable. (That is, the query engine can't make use of an index to limit the number of rows to be checked. The query engine will need to check EVERY row in the table.

I suspect that this, along with the JOIN operation, could be a major contributor to the slow performance. (I don't see that an OUTER join is required in your query, it appears to be equivalent to an INNER join, given the predicate on tweet_urls.url.

Ideally, you would not need the % wildcard character at the beginning, and you could instead check url LIKE 'cnn.com%', without a leading wildcard, which has a chance of getting the query engine to use an index (on the url column).

Obviously, some changes to the table and index definitions could help performance, but only if you have some liberty to make those changes. (Often, the posters asking questions like yours are limited in what changes they are able to make.)

So, I am addressing only the query you have, and not suggesting any schema changes. (IF I were Faced with a requirement like yours, I would be considering making changes to the tables and indexes, possibly even a FULLTEXT index.)

But you asked about changing the query, so I'm only going to address the query.

---

It looks as if you mean to return distinct urls, ordered by the created_at of the LATEST tweet, and not just by the created_at of one (not necessarily the latest) tweet.

If so, I don't believe your query is returning the rows in the order you expect, given that you are referencing a non-aggregate in the ORDER BY clause which is not included in the GROUP BY clause.

NOTE: Other relational databases will throw an exception with a statement like this, e.g. Oracle will throw ORA-00979: not a GROUP BY expression and SQL Server will throw Msg 8127 Column "tweets.created_at" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. MySQL is more liberal, which is not always a good thing.

If your query is returning rows in the order you expect, that is by happy accident, and is not due to some guaranteed behavior.

To get the rows ordered by the created_at of the LATEST tweet, you'd need a query of a different form, one that specifies that you want to sort based on the maximum created_at. As examples:

SELECT tweet_urls.url
  FROM tweet_urls
 WHERE tweet_urls.url LIKE '%cnn.com%'
 GROUP BY tweet_urls.url
 ORDER BY MAX((SELECT MAX(tweets.created_at) FROM tweets WHERE tweets.tweet_id = tweet_urls.tweet_id)) DESC LIMIT 0, 20

-- or

SELECT t.url
  FROM ( SELECT tweet_urls.url, MAX(tweets.created_at) AS max_created_at
           FROM tweets 
           JOIN tweet_urls ON tweet_urls.tweet_id = tweets.tweet_id 
          WHERE tweet_urls.url LIKE '%cnn.com%'
          GROUP BY tweet_urls.url
       ) t
 ORDER BY t.max_created_at DESC LIMIT 0, 20

---

Setup the test case you showed:

CREATE TABLE tweets (tweet_id INT UNSIGNED NOT NULL, created_at DATETIME) ENGINE=MyISAM; 
CREATE TABLE tweet_urls (tweet_id INT UNSIGNED NOT NULL, url VARCHAR(20) NOT NULL) ENGINE=MyISAM;
INSERT INTO tweets VALUES (123456, '2012-06-11 11:31:28'),(234567,'2012-06-11 11:32:55'),(345678,'2012-06-11 11:33:22');
INSERT INTO tweet_urls VALUES (123456,'cnn.com'),(123456,'fox.com'),(234567,'abc.com'),(345678,'abc.com');

And add a couple more rows:

INSERT INTO tweets VALUES (1, '2012-06-10'),(2,'2012-06-12'); 
INSERT INTO tweet_urls VALUES (1,'Xcnn.com'),(2,'Xcnn.com');

When I run your query, it returns rows in order by SOME created_at, but not necessarily by the created_at of the LATEST tweet.