Вопрос по проектированию бд mysql (интересный)

В теории реляционных моделей данных, такой вариант дизайна называется Associative Entity, и он абсолютно правильный. Иногда используется и для O:O (One:One) связей в том числе, чтобы не плодить жирные таблицы (тогда PK делают на какой-то один столбец, для которого нужно соблюдать уникальный констрейнт).

Запрос вам уже скинули, можете сделать PRIMARY KEY (ArticleId, AuthorId), и рядом повесить индекс на AuthorId, но без ArticleId.

В InnoDB PK автоматически добавляется в индекс (covering index).

Либо ещё лучше - сделать FK, с нужными вами проверками интеграционности, как в примере ниже, только добавив, например RESTRICT или CASCADE.

Вот тут три варианта запроса, все три имеют одинаковый результат, и разный план выполнения. Второй вариант скорее всего будет самый производительный на больших таблицах.

Самый правильный вариант уже указан вначале - число статей к автору. Это всего лишь повесить тригер на сохранение/удаление статьи, что бы посчитать число статей и обновить его у автора.

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

Именно в подобных случаях ничего страшного не будет.

По двум причинам:

Первая и самая важная - первоочередная выборка будет по таблице коннектору ArticleAuthors, которая даже при 2 млн записей и int-ах под ИД-шники займет всего 16мб. Выборка же из articles/authors будет уже по примариИД, т.е. реактивной в любом случае.

Вторая - то что при count(articleid) выборка вообще будет по ключам, даже без обращения к самой таблице, что еще прибавит реактивности, особенно если ключи в память попадут (а куда они денуться при таком размере).

Кстати, на заметку автору, я вот тут писал про табличку с почти 3 млрд. записей, на 77Гб, и никаких проблем с производительностью не возникало, а с партициями вообще работало супер.

Так-то он так. Но вот только в данном конкретном случае где в "автор-документ" (документ - не просто "документ", а пост на сайте) many-2-many? У 99% CMS автор - 1 шт.

Учил вас старик Аким, учил, а все без толку: "Не умножайте число сущностей сверх необходимого"

---------- Добавлено 09.05.2019 в 14:31 ----------

Не умел, но это было много лет тому как... сейчас (возможно) ситуация отличается, т.к. квалификация DBA падает стремительным домкратом, а вложенные селекты идут в гору, как простое костыльное решение

Не буду спорить, возможно и так. В любом случаи такое надо тестировать на практике.

Я ещё больше о том, что да, если знать про индексы и особенности это одно дело, а когда потом вставлять этот код уже в другие решения, где уже другие входные данные.

Для моих целей нужно учитывать, что у статей может быть несколько авторов.

Грамотно спроектировать бд - одна задача. Быстро выводить инфу - другая. И между этими двумя задачами часто используется прослойка в виде агрегации данных.

Элементарный пример - поисковый движок. Он и есть та самая прослойка. Так как поиск по бд займет куда больше времени, если участвует более чем одна таблица.

SELECT a.id, a.name
FROM author a 
JOIN (
  SELECT author_id
  FROM author_article
  GROUP BY author_id
  HAVING COUNT(*) > 2
) t ON a.id = t.author_id;

На что указывают эти сокращения a.id, a.name, author a, t ON a.id, буква ''t''?

В гугле забанили?

Читай доки.