Запрос Mysql: сортировка файла при внутреннем соединении, ограничение и порядок

Ответ 1

Он использует индекс, как говорится в объяснении.

id (1), select_type(SIMPLE), TABLE(articles_authors), TYPE(ref),
possible_keys(fk_Articles_fk_Authors, fk_Articles, fk_Authors),
KEY (fk_Authors), Key_len(4) , ref(const), ROWS(171568),
extra (USING TEMPORARY; USING FILE sort)

Только в качестве extra для 50 строк, которые он выбирает, и чем заказы по дате публикации, делает это fileort.
Он создает временную таблицу с 50 элементами. Затем он сортируется с табличным телефоном.
Этот имеет, так как MySQL не может использовать большой индекс для этих одиноких 50 элементов, это будет дорого для IO-времени доступа.

Это быстрее для сортировки на 50 номеров в памяти, а затем для доступа к индексу на диске.

Вы можете сделать что-то, чтобы ускорить запрос:

optimize table articles, articles_authors

и повторите запрос.

РЕДАКТИРОВАТЬ: Ускорить предложение путем денормализации статей таблицы

Если вы переписываете запрос следующим образом:

SELECT articles.id FROM articles WHERE articles.id IN (
  SELECT articles_authors.fk_articles WHERE articles_authors.fk_authors = 586 
  LIMIT 0,50
)
ORDER BY articles.publicationDate;

Вероятно, вы увидите ту же производительность, но это подчеркивает проблему. Если у автора 586 есть 180 000 статей, тогда MySQL должен искать 50 элементов из 180k в статьях_авторов, а затем снова искать 50 элементов из 180k в таблице заказов.

Если вы объедините таблицы article_authors и articles, ваши статьи в таблице будут денормализованы (при условии, что статья может иметь несколько авторов), но вам не нужно делать соединение, и вы сохраните себе второй поиск.

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `publicationDate` date NOT NULL DEFAULT '1970-01-01',
  `title` varchar(255) NOT NULL,
  `fk_Authors` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Articles_fk_Authors` (`id`,`fk_Authors`),
KEY `fk_Authors` (`fk_Authors`),
KEY `publicationDate` (`publicationDate`)
) ENGINE=MyISAM AUTO_INCREMENT=2349047 DEFAULT CHARSET=utf8 

Теперь вы можете выбрать его таким образом

SELECT articles.id FROM articles WHERE articles.Author = 586 
ORDER BY articles.publicationDate LIMIT 50,0

Ответ 2

Возможно, это поможет вам:

SELECT articles.id 
    FROM articles 
        INNER JOIN (SELECT fk_Articles FROM articles_authors WHERE articles_authors.fk_Authors=586) sub ON articles.id=sub.fk_Articles 
ORDER BY articles.publicationDate LIMIT 0,50;

Ответ 3

SELECT articles.id 
FROM articles 
INNER JOIN articles_authors ON articles.id=articles_authors.fk_Articles 
WHERE articles.id=586 
ORDER BY articles.publicationDate LIMIT 0,50;

Ответ 4

Не уверен, но предложение Conrad, похоже, меняет сортировку и ограничение, поэтому вы можете получить первые 50 элементов случайного списка в отсортированном порядке вместо первых 50 элементов отсортированного списка.

Может ли представление с помощью справки join, если оно упорядочено по fk_author, publishDate и имеет индекс? Также зависит от того, что вы оптимизируете, скорость или дисковое пространство?

Можете ли вы использовать IN в Mysql? Может быть, лучше оптимизировать? (пример кода, не отмечен)

SELECT id FROM articles WHERE id IN 
(SELECT fk_Articles FROM articles_authors WHERE fk_Authors=586) as IDs
ORDER BY publicationDate LIMIT 0,50;

Ответ 5

Это может быть действительно, в зависимости от ваших данных.

SELECT articles.id 
FROM articles 
INNER JOIN articles_authors ON articles.id=articles_authors.fk_Articles 
WHERE articles_authors.fk_Authors=586 
ORDER BY articles.publicationDate LIMIT 0,50;

Если articles_authors.fk_Authors = 586 приводит к достаточно редким рядам в соответствии со статистикой, собранной вашим движком БД, будет дешевле получить все и получить 50 лучших строк.

Если, напротив, это приведет к большей части статей, будет дешевле проконсультироваться с индексом на articles.publicationDate и отфильтровать недопустимые строки, пока вы не запросите 50 строк.