Спасибо, что сидите тут со мной)

Поразительно, выдавил все запросы в лог, выполнил всю эту конструкцию в консоли, не оскверненную всяческими сомнительными скриптами - процесс занял 40мс. Быстрее уже некуда. Другое дело) Бум крутить, почему двиг неверно считает время... Сенкс.

Да, а этот запрос из другой оперы был...

Прочитал про FOREIGN KEY - действительно, я не правильно его охарактеризовал. Оказывается, мускул их то ли не поддерживает вовсе, либо поддерживает половинчато. Видать, все в голове перемешалось просто.

Набросал от руки чисто механический запрос, удовлетворяющий моему условию. В определениях могу путаться, поэтому легче просто показать все на примере:


Он выводит 20 комментариев к новости номер 2352. У нас происходит выборка один-ко-многим: `users`.`id` у нас уникален, тогда как значений `comments`.`user_id` может быть большое количество (один юзер может добавить множество комментов к одной новости). Вот... Тогда на столбец `users`.`id` ставим PRIMARY KEY, на `comments`.`user_id`- просто KEY (ибо джойним с `users`.`id`по нему). И на `comments`.`post_id`тоже просто KEY (потому-что выборка по нему). Кроме как по `comments`.`post_id` выборка не осуществляется (нет необходимости).

Я правильно поступаю?

Это сообщение отредактировал(а) Acuna - 5.1.2014, 15:12

Полностью. Но только на одном движке.


Изучите, что такое EXPLAIN. И главное - как интерпретировать его вывод. Затем используйте. 
Без этих сведений ничего сказать нельзя.

Очень вероятно, что разница как раз между холодным (с диска) и тёплым (из кэша в памяти) чтением. Да, на 200k записей при seqscan будет где-то пол-секунды при холодном и 40мс при тёплом. Холодное -- при условии, что движок БД всё-таки в памяти, т.е. действия с другими таблицами недавно выполнялись
При этом при index scan с одной записью -- будет около 40-100 мс при холодном и 0.5 мс при тёплом.



ещё раз: главное, что это -- не важно. Это не средство улучшэния производительности, это средство поддержания цэлостности.



Не особенно.



users.id является практически в любом случае PRIMARY KEY -- независимо от того, какие конкретно запросы ты проектируешь. Поскольку именно id является основным идэнтификатором пользователя в твоей базе данных. Так что хочешь-не хочешь -- а users.id надо делать PRIMARY KEY.
Вообще, на начальном этапе освоения -- PRIMARY KEY надо делать в любой таблицэ. Потом могут быть какие-нибудь не очень реляцыонные примеры таблиц, в которых не нужэн PRIMARY KEY -- но в первые пол-года тебе таких не попадётся.
Ну и да, так получилось, что на PRIMARY KEY всегда есть индэкс.



1) comments.user_id не является ключом в таблицэ comments -- поскольку очевидно, что не идэнтифицырует однозначно кортэж.
2) Именно поэтому не стоит называть такие индэксы, которые делаются только для ускорения работы запросов, ключами. Это не ключи!!! Это именно индэксы, средство для ускорения работы движка БД.
3) К тому жэ, CREATE KEY вместо CREATE INDEX -- это какое-то местечковое оракловское расшырение. В большынстве БД есть ограничения PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY (всё именно в таком написании), а просто KEY -- нет. И, отдельно, есть индэксы -- на создание которых нет особо стандартов, но чаще всего их можно создать через CREATE INDEX.

При этом некоторые ограничения создают некоторые индэксы для своей работы, но это побочный эффект в общем-то.

То есть -- для ускорения работы ковыряния ужэ должны быть в индэксах, а не в ключах.



При сканировании таблицы comments (возможно, многократном за один запрос, это сейчас неважно) -- очевидно, что индэксированная выборка будет по одному столбцу из этих двух. Поскольку либо у нас сначала выбирается что-то из comments по post_id, add_date, тогда comments.user_id у нас есть, и по нему ищется ужэ кортэж в таблицэ users -- либо что-то выбирается из таблицы users, по нему ищутся все комментарии с данным comments.user_id в таблицэ comments, и post_id индэксированно искать ужэ не надо, посколько он ужэ прочитан и получен. Есть, кстати, варианты, когда вообще никаких индэксов использоваться не будет -- обе таблицы прочитаются, отсортируются в памяти и сольются в результат последовательным перебором отсортированных значений. Но это не тот случай, конечно.
Кстати, почитайте про организацыю btree index, это полезно.

Узнать, по какому столбцу реально будет выборка -- поможэт explain, понять, по какому она Вам нужна -- поможэт попытка прикинуть оптимальный план выполнения на средне-нормальных Вашых данных в уме или на листочке.

Да, скажу ответ для данного запроса и типичного соотношэния размеров таблиц: относительно правильным будет создать индэкс на comments (post_id, add_date) -- чтобы движок мог чисто по индэксу определить первую запись из comments, которая нам нужна, а потом ужэ для всех 20 подряд нужных записей выбирать их идя по индэксу. Ну, и на каждую --  по одной записи по PRIMARY KEY из users.

Относительно правильным -- поскольку при большом limit движку придётся выбирать все соответствующие строки. Т.е. если нужна 100-я страница темы -- то никаких индэксов, чтобы отсчитать 2000-й коммент с данным post_id у него не будет, и придётся пересчитывать все 2000 записей. Более правильным будет либо указывать в html-ссылках на страницы даты, с которой эта страница начинается (ну там, before=2012-11-25+14:21:15.12312) или перенумеровать комменты темы отдельным полем сделать на него индэкс и выбирать ужэ по этому полю, а не по лимиту.

Ну и, в общем, если таблица users относительно невелика и висит в памяти -- то можэте дажэ уложыться в нормальные 50мс.

Умрет и винты взорвуться попутно разнеся датацентр на куски. Число жертв будет исчисляться миллионами...

Гуглите:

• нагрузочное тестирование
• профилирование запросов
• профилирование приложений

Вопросов будет много меньше

Это не шардниг, это копипаст! Тут явно какая-то кривость с ключом, туда и копать нужно, в первую очередь, и если уж есть уверенность что с ключами все хорошо, можно посмотреть на партиционирование! А вертикальный и горизонтальный шардинг строится совсем по другим принципам, хотя легкая внешняя схожость с шардингом есть, но только внешне!

users.id - вешаешь primary key
comments.user_id - вешаешь foreign key, который ссылается(references) на users.id
comments.post_id - рискну предположить, что тоже можно повесить primary key, но тут нужно смотреть структуру таблицы comments

Скажыте, а Вам до этого кто-нибудь говорил, что не надо лишний раз высказывать суждения о вещах, в которых Вы не разбираетесь?

Это сообщение отредактировал(а) tzirechnoy - 9.1.2014, 12:10

Страдаете звездной болезнью??

Наслаждаюсь.

Ничего, это тоже лечится! Лучшее средство лечения - работа!

Большой брат внимательно наблюдал за вами со стороны! ;)

Хотел бы поблагодарить всех за отличные мысли, надо их все обмозговать теперь...

Особенно мне понравился ответ Bulat'а, кратко и по существу, особенно, когда работы столько, что забываешь, как тебя зовут 

А сам шардинг - дело вообще не мое, данный крупный проект некоторое время меняет хозяев, это подобие шардинга создавал прошлый владелец, а моя работа как PHP-программиста касаемо БД заключается только в написании запросов, сами БД проектируют другие люди. А теперь я, получив этот проект, вдруг нечаянно осознал, что я авно, и ничего в этой жизни не добился  В следствии чего и появилась данная тема!)

tzirechnoy,
А можно вам задать вопрос не по существу: давно хотел спросить, а почему вы вместо "е" пишите "э", а вместо "и" - "ы"?)))