Форум пользователей MySQL

Retrill · 08.01.2011 22:36:57

В общем случае задача сводится к определению, есть ли перекрывающиеся значения в двух строках типа SET.
Пусть первая строка выглядит как 'a,c,h', а вторая как 'b,h,g,i,w'. Есть ли встроенная функция, которая вернет 1, если в этих строках встречаются одинаковые значения (в данном примере это h), и 0, если нет. Спасибо!

Отредактированно Retrill (08.01.2011 22:37:23)

Retrill · 08.01.2011 23:13:11

Думал-думал, и сам решил. Вот такой вариант:

Пусть есть таблица:

CREATE TABLE temp(a SET('bio','mat','psy'),b SET('bio','mat','psy'));

INSERT INTO temp(a,b) VALUES('mat,psy','bio,mat');

INSERT INTO temp(a,b) VALUES('psy','bio,mat');

"Смысл" ее заключается в том, что столбец "a" - это, допустим, учащиеся тех классов, которые не должны просматривать эту запись, а столбец "b" - это список классов, к которым относится данный учащийся. Вот мы и хотим понять, показывать ему эту запись или нет.
Мы хотим выбрать из таблицы те строки, в которых присутствуют учащиеся, которые могут их просматривать. Как можно сделать:

SELECT * FROM temp WHERE NOT ((a+0)&(b+0));

 

Этот запрос возвращает строку, где "a"="psy", а "b"="bio,mat".

Вот только проблема в том, что в данном запросе, насколько мне известно, нельзя будет использовать индекс для ускорения выборки. Это как-то можно реализовать?..
В данном примере, конечно, можно создать столбец "c", где будет отражен результат "NOT ((a+0)&(b+0))", и делать выборку по нему. Но, представим, что в таблице есть только значения столбца "a", и заходит пользователь со своей комбинацией из списка 'bio','mat','psy'. Нам нужно выбрать строки из таблицы, которые можно ему просматривать. Как тут индекс использовать, и какой вообще можно?..

Отредактированно Retrill (08.01.2011 23:17:11)

paulus · 09.01.2011 03:10:23

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

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

Ах, да, и, конечно, Вы перемудрили с выражением, можно было написать просто
WHERE a & b — это же просто наборы битов.

P.S. Но если хотите остаться в рамках SETов — можете сделать некоторый костыль
(но я всё-таки советую нормализованную схему!):
WHERE a IN ('mat', 'bio,mat', 'bio,mat,psy', ...) — выражение будет сложнее, но
зато сможет использовать индекс.

Retrill · 09.01.2011 13:31:35

paulus написал:
А почему Вы не хотите нормализовать данные, и хранить список курсов, на которые
подписан человек, в отдельной таблице? SET никогда не подразумевался как тип
для поля, по которому будут искать.

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

Хм... Не совсем понял идею.
Допустим, делаю вторую таблицу, как вы и говорите, где храню список спецкурсов. Вы имеете в виду примерно следующую:

CREATE TABLE courses(course_id TINYINT,label VARCHAR(10));

 

Добавляю в нее где-то 26 значений, а дальше что делать? Любой человек может выбрать хоть сколько, от 0 до 26 этих курсов. В каком поле хранить его выбор? Как вариант (мой поток мысли по вашей идее):
Пусть помимо таблицы пользователей я сделаю отдельную таблицу с двумя полями типа (user_id INT,course_id TINYINT). Для каждого пользователя в ней будет несколько строк.

CREATE TABLE users (user_id INT, [другие поля]);

CREATE TABLE selected_courses(user_id INT,course_id TINYINT);

Далее. Пусть есть информация о мероприятиях (событиях), где содержится информация о том, посетителям каких курсов нельзя их посещать. Реализуем также с помощью двух таблиц:

CREATE TABLE events (event_id INT, [другие поля]);

CREATE TABLE resctricted_courses(event_id INT,course_id TINYINT);

Далее я хочу выбрать значения каких-то полей из таблицы events для тех событий, которые может посещать данный пользователь (известен его user_id). Причем с использованием индексов, иначе просто смысла нет в этой затее (без них уже реализовано).

Запрос с JOIN-ами что-то не получается записать. Похоже, таблицу restricted_courses нужно будет переделать в таблицу allowed_coruses, где хранится информация о том, люди каких курсов могут посещать эти мероприятия. Так? Или можно написать адекватный запрос, основанный на индексах, в рамках описанной структуры?..

P.S.:

paulus написал:
Но если хотите остаться в рамках SETов — можете сделать некоторый костыль
(но я всё-таки советую нормализованную схему!):
WHERE a IN ('mat', 'bio,mat', 'bio,mat,psy', ...) — выражение будет сложнее, но
зато сможет использовать индекс.

Я же не знаю, какой набор курсов будет у данного пользователя, придется формировать эту строку динамически с использованием prepared statements. Сложновато получится.

Отредактированно Retrill (09.01.2011 13:33:52)

Retrill · 09.01.2011 18:19:34

Еще подумав насчет запроса, написал вот такой:

SELECT * FROM events WHERE event_id NOT IN(SELECT event_id from restricted_courses INNER JOIN selected_courses USING(course_id) WHERE user_id=1);

 

Работает, вроде, так, как надо. Однако возникает вопрос относительно оптимизации запроса и использования индексов.

Сейчас вот решил протестировать ситуацию с полями SET.
Добавил в таблицу users поле status SET('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32');
Аналогично добавил в таблицу events поле restricted SET('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32');

Имеется в виду, что в поле "status" отражены номера курсов, которые выбрал студент, а в поле "restricted" отражены номера курсов, посетители которых не должны просматривать эту строку.
Внес в таблицу users 1000 записей, а в таблицу events 10000. В каждом поле SET было случайно выбрано 10 битов (из 32).
Выполнил запрос:

SELECT COUNT(*) FROM events JOIN users WHERE NOT (status&restricted) AND user_id=500;

 

На выполнение ушло 0,05 сек.
А подобный запрос...

SELECT COUNT(*) FROM events WHERE event_id NOT IN(SELECT event_id from restricted_courses INNER JOIN selected_courses USING(course_id) WHERE user_id=1);

 

...выполняется за 3,54 сек. Это при существовании индексов по полям таблиц users, events, restricted_courses и selected_courses, без индексов вообще минуты выполняется.
Стоит ли тогда париться вообще, использовать биты и все?..

Отредактированно Retrill (09.01.2011 21:59:45)

paulus · 10.01.2011 16:38:04

А какие индексы добавили? И EXPLAIN от запроса покажите, пожалуйста.

Retrill · 10.01.2011 21:25:19

paulus написал:
А какие индексы добавили? И EXPLAIN от запроса покажите, пожалуйста.

Таблица users: индекс по (user_id);
Таблица events: индекс по (event_id);
Таблица selected_courses: 2 индекса по (user_id),(course_id);
Таблица restricted_courses: 2 индекса по (event_id),(course_id);
Индексы по нескольким полям не использовались, ибо непонятно мне, какие индексы тут вообще нужны.

EXPLAIN SELECT count(*) FROM events WHERE event_id NOT IN(SELECT event_id from restricted_courses INNER JOIN selected_courses USING(course_id) WHERE user_id=789);

 

http://i13.fastpic.ru/big/2011/0110/bf/bff0c88d0b0017169db7edc2a233a3bf.jpg

EXPLAIN SELECT COUNT(*) FROM events JOIN users WHERE user_id=500 AND NOT (status&restricted);

http://i13.fastpic.ru/big/2011/0110/db/de3ef09e12f6f675522df2543203f9db.jpg

Отредактированно Retrill (10.01.2011 21:42:21)

paulus · 10.01.2011 23:07:43

Хорошо, убедили, в этом случае действительно читать одну таблицу быстрее, чем
несколько. Скорее всего, вообще в случае «все, кроме нескольких» Вы будете
выигрывать на одной таблице. Даже если запрос правильно переписать так, чтобы
он был независимым.

Форум пользователей MySQL

#1 08.01.2011 22:36:57

Пересечение множеств типа SET

#2 08.01.2011 23:13:11

Re: Пересечение множеств типа SET

#3 09.01.2011 03:10:23

Re: Пересечение множеств типа SET

#4 09.01.2011 13:31:35

Re: Пересечение множеств типа SET

paulus написал:

paulus написал:

#5 09.01.2011 18:19:34

Re: Пересечение множеств типа SET

#6 10.01.2011 16:38:04

Re: Пересечение множеств типа SET

#7 10.01.2011 21:25:19

Re: Пересечение множеств типа SET

paulus написал:

#8 10.01.2011 23:07:43

Re: Пересечение множеств типа SET

Board footer