Меню
Что нового?

Поругайте структуру БД

kuchin

kuchin

Новичок
Поругайте структуру БД

Посмотрите пожалуйста, скажите что вам тут не нравится и вы бы сделали по другому:

(проект MyProgs)

Таблица программ:
Код: 
id          INT(10)
name        VARCHAR(250) - имя программы
hash        VARCHAR(32)
description VARCHAR(250)
version     VARCHAR(30)
usercount   SMALLINT(6) - пересчитывается каждый раз, 
                          когда юзер добавляет/удаляет программу
Таблица связей программ и юзеров:
Код: 
id          INT(11)
program     INT(10) - id из таблицы программ
member      INT(10) - id из таблицы юзеров
description VARCHAR(250) - user-specific
version     VARCHAR(30) - user-specific
added       INT(10) - дата
Таблица тагов:
Код: 
id        INT(10)
tag       VARCHAR(30) - имя тага
progcount SMALLINT(6) - пересчитывается каждый раз, 
                        когда этот таг добавляют/удаляют к какой-нибудь программе
Таблица связей программ и тагов:
Код: 
id      INT(11)
program INT(10) - id из таблицы программ
tag     INT(10) - id из таблицы тагов
member  INT(10) - id из таблицы юзеров - в принципе, этот элемент 
                  можно достать из таблицы программа/юзер, 
                  но "кэширование" его тут позволяет 
                  убрать лишний INNER JOIN.
Таблицу пользователей давать не буду, там все просто.
На данный момент это бежит на Apache 2.0, MySQL 3.23, собираюсь переносить на более новую версию, чтобы воспользоваться SELECT'ами внутри SELECT'ов и транзакциями (4.1 вроде должно хватить для этого? Или надо сразу 5?). PHP 4.3, может быть перейду на 5 вскоре.
 

Полукондуктор

Новичок
А зачем в таблицах связей отдельный ID? Имхо первичным ключом может быть пара ID-ов из тех таблиц, которые связываем. Или там связь не один к одному?
 
kuchin

kuchin

Новичок
Вообще да, один к одному, т.е. один и тот же пользователь может добавить определенную программу один раз, и один раз дать ей определенный таг. Т.е. в первом случае первичным ключом будет два id, во втором - три. Имеет смысл так сделать? "Сколько" я выиграю?
 

Полукондуктор

Новичок
Большой выигрыш по производительности и ресурсам ты с этого не получишь. Вопрос просто в рациональности. Смотри сам - может быть отдельный ID тебе действительно понадобится (для упрощения програмного кода, например).
 
kuchin

kuchin

Новичок
Я им действительно пользуюсь, так что наверное оставлю именно так. Индексы у меня по остальным id стоят, просто они не первичные. А кроме этого все выглядит нормально?
 
Falc

Falc

Новичок
kuchin
>>Индексы у меня по остальным id стоят, просто они не первичные.
Я бы тогда сделал составной уникальный.
 
kuchin

kuchin

Новичок
У меня еще один вопрос...
Когда мне нужно получить список программ, у которых есть таги а,б,в (их пересечение), я это делаю так:
SELECT p.* FROM programs p
INNER JOIN progs_tags t0 ON p.id = t0.program AND t0.tag = 'a'
INNER JOIN progs_tags t1 ON p.id = t1.program AND t1.tag = 'б'
INNER JOIN progs_tags t2 ON p.id = t2.program AND t2.tag = 'в'

Это работает более-менее приемлемо до трех тагов, когда я делаю запрос на 4 и больше таких INNER JOIN'a - он занимает очень много времени, если вообще не затыкается.
Это можно решить по другому? Или может быть у меня должны присутствовать какие-то индексы, которых у меня нет?
 
kuchin

kuchin

Новичок
Потому что такой запрос всегда вернет 0 результатов при поиске больше чем одного тага...

-~{}~ 30.08.05 15:38:

В дополнение: когда я ищу по тагам одного и того же пользователя, то все работает очень быстро, т.к. условие внутри INNER JOIN идет еще и по member id, а когда без этого, то намного медленнее...
 

alpine

Новичок
kuchin
Сорри, не прав.

-~{}~ 30.08.05 16:36:

А попробуй примерно так:
[sql]
CREATE TEMPORARY TABLE tmp
SELECT DISTINCT p.id, p.name, p2t.tags_id FROM programs AS p
LEFT JOIN `programs2tags` AS p2t ON p.id=p2t.programs_id
WHERE
p2t.tags_id=1
OR
p2t.tags_id=2
OR
p2t.tags_id=3;

SELECT
*
FROM
tmp
GROUP BY id
HAVING COUNT(*)=3;

DROP TABLE tmp;
[/sql]
 
kuchin

kuchin

Новичок
Это тоже не то :)
Мне нужно пересечение, т.е. те программы, которым (может быть разные) люди добавили и таг 1 и таг 2 и таг 3, а не или таг 1 или таг 2 или таг 3...
 

alpine

Новичок
kuchin
Я это понял, второй запрос как раз это и должен делать.
 
Falc

Falc

Новичок
alpine
Для твоего примера временая таблица не нужна:
SELECT p.*
FROM programs AS p
JOIN `programs2tags` AS p2t ON p.id = p2t.programs_id
WHERE p2t.tags_id = 1 OR p2t.tags_id = 2 OR p2t.tags_id = 3;
GROUP BY id
HAVING COUNT( * ) = 3;


kuchin
А почему тормозит:
SELECT p.* FROM programs p
INNER JOIN progs_tags t0 ON p.id = t0.program AND t0.tag = 'a'
INNER JOIN progs_tags t1 ON p.id = t1.program AND t1.tag = 'б'
INNER JOIN progs_tags t2 ON p.id = t2.program AND t2.tag = 'в'

???

Покажи explain
 
kuchin

kuchin

Новичок
Ну на самом деле запрос чуть сложнее...
Код: 
explain SELECT DISTINCT p.*  FROM items AS p 
INNER JOIN tags_of AS t0 ON p.itemid = t0.itemid AND t0.tagid =  
(SELECT tagid FROM tags WHERE tag='windows') 
INNER JOIN tags_of AS t1 ON p.itemid = t1.itemid AND t1.tagid =  
(SELECT tagid FROM tags WHERE tag='free') 
INNER JOIN tags_of AS t2 ON p.itemid = t2.itemid AND t2.tagid =  
(SELECT tagid FROM tags WHERE tag='opensource') WHERE 1   
ORDER BY p.usercount DESC LIMIT 0, 10;
Код: 
+----+-------------+------------+--------+-----------------------------------------+---------+---------+------------------------+------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys                           | key     | key_len | ref                    | rows | Extra                                                     |
+----+-------------+------------+--------+-----------------------------------------+---------+---------+------------------------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | t2         | ref    | PRIMARY,itemid,tagid,itemmember,tagitem | tagid   |       4 | const                  | 1177 | Using where; Using index; Using temporary; Using filesort |
|  1 | PRIMARY     | p          | eq_ref | PRIMARY                                 | PRIMARY |       4 | progs.t2.itemid        |    1 |                                                           |
|  1 | PRIMARY     | t1         | ref    | PRIMARY,itemid,tagid,itemmember,tagitem | tagitem |       8 | const,progs.p.itemid   |    1 | Using where; Using index; Distinct                        |
|  1 | PRIMARY     | t0         | ref    | PRIMARY,itemid,tagid,itemmember,tagitem | tagitem |       8 | const,progs.p.itemid   |    1 | Using where; Using index; Distinct                        |
|  4 | SUBQUERY    | items_tags | const  | tag                                     | tag     |      31 |                        |    1 | Using index                                               |
|  3 | SUBQUERY    | items_tags | const  | tag                                     | tag     |      31 |                        |    1 | Using index                                               |
|  2 | SUBQUERY    | items_tags | const  | tag                                     | tag     |      31 |                        |    1 | Using index                                               |
+----+-------------+------------+--------+-----------------------------------------+---------+---------+------------------------+------+-----------------------------------------------------------+
-~{}~ 30.08.05 17:16:

Но даже если убрать вложенные SELECT'ы и заменить их на заранее найденные числа, добавление четвертого INNER JOIN удлинняет запрос до нескольких минут если не больше...
 

alpine

Новичок
Falc
Вот тестовые данные, проверь.
Код: 
CREATE TABLE `programs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

#
# Дамп данных таблицы `programs`
#

INSERT INTO `programs` VALUES (1, 'program1');
INSERT INTO `programs` VALUES (2, 'program2');

# --------------------------------------------------------

#
# Структура таблицы `programs2tags`
#

CREATE TABLE `programs2tags` (
  `programs_id` int(11) NOT NULL default '0',
  `tags_id` int(11) NOT NULL default '0'
) TYPE=MyISAM;

#
# Дамп данных таблицы `programs2tags`
#

INSERT INTO `programs2tags` VALUES (1, 1);
INSERT INTO `programs2tags` VALUES (1, 2);
INSERT INTO `programs2tags` VALUES (1, 3);
INSERT INTO `programs2tags` VALUES (1, 4);
INSERT INTO `programs2tags` VALUES (2, 1);
INSERT INTO `programs2tags` VALUES (1, 1);
INSERT INTO `programs2tags` VALUES (1, 2);
 
Falc

Falc

Новичок
alpine

У меня нет мускула :)
В чем там проблема ?

-~{}~ 30.08.05 18:24:

kuchin
А для 4-х джойнов покажи explain
 

alpine

Новичок
Falc
[sql]
SELECT *
FROM programs AS p
JOIN `programs2tags` AS p2t ON p.id = p2t.programs_id
WHERE p2t.tags_id =1 OR p2t.tags_id =2 OR p2t.tags_id =3
[/sql]
Код: 
id 	name 	programs_id 	tags_id 	
1	program1	1	1	
1	program1	1	2	
1	program1	1	3	
2	program2	2	1	
1	program1	1	1	
1	program1	1	2
[sql]
SELECT * , COUNT( * ) AS cnt
FROM programs AS p
JOIN `programs2tags` AS p2t ON p.id = p2t.programs_id
WHERE p2t.tags_id =1 OR p2t.tags_id =2 OR p2t.tags_id =3
GROUP BY p.id
[/sql]
Код: 
id 	name 	programs_id 	tags_id 	cnt 	
1	program1	1	1	5	
2	program2	2	1	1
 
Falc

Falc

Новичок
alpine
У тебя теги по нескольку раз повторяются, сделай первичный ключ на programs2tags и будет ок.
 
Войдите или зарегистрируйтесь для ответа.
Сверху