GROUP BY с ORDER BY очень медленно работает с базой данных (кажется, создает временную таблицу)

Я думаю, что настоящая проблема здесь:

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

Этот тип запроса (LIKE без постоянного префикса) не может эффективно использовать индекс.

Вы можете решить эту проблему, добавив в таблицу дополнительный столбец с именем domain и проиндексировав его. Затем вы можете изменить свой запрос на:

WHERE tweet_urls.domain = 'cnn.com'

Поместите индекс в столбец tweets.created_at

Прежде чем настраивать оператор, убедитесь, что оператор гарантированно возвращает правильный набор результатов, который вы ожидаете. (увидеть ниже)

Что касается производительности, то предикат LIKE '%foo' (с начальным подстановочным знаком) не подлежит анализу. (То есть механизм запросов не может использовать индекс для ограничения количества проверяемых строк. Механизм запросов должен будет проверять КАЖДУЮ строку в таблице.

Я подозреваю, что это, наряду с операцией JOIN, может быть основным фактором низкой производительности. (Я не вижу, чтобы в вашем запросе требовалось ВНЕШНЕЕ соединение, оно похоже на ВНУТРЕННЕЕ соединение, учитывая предикат в tweet_urls.url.

В идеале вам не нужен подстановочный знак % в начале, и вместо этого вы можете проверить url LIKE 'cnn.com%' без начального подстановочного знака, что может заставить механизм запросов использовать индекс (по столбцу url).

Очевидно, что некоторые изменения в определениях таблиц и индексов могут повысить производительность, но только в том случае, если у вас есть свобода вносить эти изменения. (Часто плакаты, задающие такие вопросы, как ваш, ограничены в том, какие изменения они могут внести.)

Итак, я обращаюсь только к вашему запросу и не предлагаю никаких изменений схемы. (ЕСЛИ бы я столкнулся с таким требованием, как ваше, я бы подумал о внесении изменений в таблицы и индексы, возможно, даже в полнотекстовый индекс.)

Но вы спрашивали об изменении запроса, поэтому я собираюсь только обратиться к запросу.

Похоже, вы хотите вернуть разные URL-адреса, упорядоченные по created_at самого ПОСЛЕДНЕГО твита, а не только по created_at одного (не обязательно самого последнего) твита.

Если это так, я не верю, что ваш запрос возвращает строки в том порядке, в котором вы ожидаете, учитывая, что вы ссылаетесь на неагрегатное выражение в предложении ORDER BY, которое не включено в предложение GROUP BY.

ПРИМЕЧАНИЕ. Другие реляционные базы данных будут генерировать исключение с таким оператором, например. Oracle выбросит ORA-00979: not a GROUP BY expression, а SQL Server выбросит 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 более либерален, что не всегда хорошо.

Если ваш запрос возвращает строки в том порядке, в котором вы ожидаете, это происходит по счастливой случайности, а не из-за какого-то гарантированного поведения.

Чтобы получить строки, упорядоченные по created_at самого ПОСЛЕДНЕГО твита, вам понадобится запрос другой формы, который указывает, что вы хотите сортировать на основе максимального created_at. В качестве примеров:

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

Настройте тестовый пример, который вы показали:

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');

И добавьте еще пару строк:

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

Когда я запускаю ваш запрос, он возвращает строки в порядке НЕКОТОРЫХ created_at, но не обязательно created_at САМОГО ПОСЛЕДНЕГО твита.