count() и несколько таблиц

[berkut]

count() и несколько таблиц

подскажите с запросом для подсчёта количества записей из нескольких таблиц. упрощённая до минимума структура

CREATE TABLE users (
  id mediumint(9) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;

CREATE TABLE friends (
  user_id mediumint(9) unsigned NOT NULL default '0' COMMENT 'внешний ключ для users.id',
  friend_id mediumint(9) unsigned NOT NULL default '0'
) ENGINE=MyISAM;

CREATE TABLE items (
  id int(11) unsigned NOT NULL auto_increment,
  owner_id mediumint(9) unsigned NOT NULL default '0' COMMENT 'внешний ключ для users.id',
  PRIMARY KEY  (id)
) ENGINE=MyISAM;

Нужно выбрать всех пользователей(users) и при этом подсчитать, сколько у этого пользователя "items" и сколько "friends". Естественно, таблицы users-items и users-friends связанны отношением один ко многим. Из-за этого и вся проблема. Такой запрос не правильно работает:

SELECT u.*, COUNT(f.user_id) AS friends, COUNT(i.owner_id) AS items
FROM users u
    LEFT JOIN friends f ON u.id = f.user_id
    LEFT JOIN items i ON u.id = i.owner_id
GROUP BY u.id

 

[jonjonson]

Такой запрос не правильно работает

По каким причинам сделан вывод?

 

[berkut]

это просто пост, чтоб было? ну как по каким? по результату. и исходя из того, как работает джоин. во friends & items всегда будет одно число, которое равно большему из кол-ва items & friends.

пока допетрил до такого:

SELECT u.*, COUNT(DISTINCT f.friend_id) AS friends, 
    COUNT(DISTINCT i.id) AS items
FROM users u
    LEFT JOIN friends f ON u.id = f.user_id
    LEFT JOIN items i ON u.id = i.owner_id
GROUP BY u.id

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

 

[jonjonson]

Нихрена не понял... Разве количество одного и того же друга и айтема может быть несколько для одного юзера?
Или может проще два запроса...

 

[berkut]

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

 

[jonjonson]

berkut, да, ты был прав (сделал тестовый пример).
Вот это объясни:

но стрёмно то, что кол-во рядов в джоине чудовищное будет

 

[berkut]

jonjonson вообще-то я создал тему и задал вопрос и мне-же приходится объяснять тебе элементарные вещи. советую п.дюбуа mysql - второе издание

 

[jonjonson]

berkut, я читал дюбуа и что? Ты делал запрос на реальных данных и время его исполнения тебя не устроило?

 

[kos]

попробуй так

SELECT u.*,
(SELECT COUNT(*) FROM friends f WHERE u.id = f.user_id) AS friends, 
(SELECT COUNT(*) FROM items i WHERE u.id = i.owner_id) AS items
FROM user u

 

[berkut]

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

 

[kos]

стоит проверить эксперементально, я думаю что подзапросы быстрее конечно могу ошибаться
если большая нагрузка, лучше сделать поля - счетчики

 

[kruglov]

Я думаю, обычно подзапросы медленнее джойнов (не зря их так долго в MySQL не добавляли). Хотя, раз у нас тут выборка всех юзеров подряд...

Мне кажется, все нормальные решения в теме уже озвучены, осталось из них выбрать наиболее оптимальное опытным путем.

 

[berkut]

kruglov

Я думаю, обычно подзапросы медленнее джойнов

об этом сказано в доке, в разделе подзапросов.
Другое дело, что опытным путём можно подобрать оптимальный вариант только для текущего состояния бд. Сейчас проект только стартовал, у каждого юзера в среднем по 3 объекта и 1-му "другу" - тесты на этой базе показывают превосходство по скорости варианта с джоинами. А в дальнейшем как будет - хз.
Похоже, оптимально всё-таки денормализовать базу и хранить кол-во "friends" & "items" для каждого юзера

 

[Wicked]

я бы для начала добавил пару индексов, добавил бы еще по одному другу каждому юзеру, затем сделал бы explain

 

[Raziel[SD]]

По хорошему, если запрос "сколько ?" гораздо чаще добавления/удаления items/friends, такой запрос даже не делать, а добавить юзеру поля "кол-во items" и "кол-во friends", и менять это значение при добавлении/удалении items/friends.

 

[kruglov]

Raziel[SD]
По-хорошему, это написано через пост выше вашего.

 

[Raziel[SD]]

kruglov
ох, сори, пропустил.