НЕ совсем тривиальный запрос для смешной вроде задачи

[Red Hat]

НЕ совсем тривиальный запрос для смешной вроде задачи

MySQL 4.1.7

Есть универсальная таблица properties со свойствами подписчиков
например

number property value
12345 sex m
12345 age 29
12345 nick george
12345 height 175
21312 sex m
21312 age 29
21312 height 190
14221 sex w
14221 age 17
14221 height 160

Например требуется выбрать всех мужчин 29 лет и еще вывести (а также и отсортировать) их по кол-ву существующих свойств

Т.е в результате выполнения должно быть следующее:
12345 4
21312 3

Запрос вида
SELECT number
FROM properties
WHERE number
IN (SELECT number
FROM `properties`
WHERE property = 'sex'
AND value = 'm'
)
AND property = 'age'
AND value = '29';
конкретно тяжелый, у меня он гоняется 90 секунд...

После колдования получил следующее
SELECT number, cntallsum FROM (
SELECT number, sum( cnt ) AS cntsum, sum( cntall ) AS cntallsum FROM (
SELECT number , count( number ) AS cnt, '0' AS cntall FROM properties WHERE (property = 'sex' AND value = 'm') OR (property = 'age' AND value = '29') GROUP BY number
UNION ALL
SELECT number , '0' AS cnt, count( number ) AS cntall FROM properties WHERE property IN ('nick', 'sex', 'age', 'height', 'weight', 'goal', 'details') AND `value` != '' GROUP BY number
) AS tmptable
WHERE (cnt =2 || cnt =0)
GROUP BY number ORDER BY cntsum DESC) AS resulttable WHERE cntsum =2;
Летает, но... надеюсь понятно... ))))

Есть предложения по оптимизации запроса?

 

[tashkentchi]

SELECT number, COUNT(*) FROM properties GROUP BY number

 

[baev]

SELECT number, COUNT(*) FROM properties WHERE property IN ('sex', 'age') AND value IN ('m','29') GROUP BY number

 

[Red Hat]

Оба варианта не то.
Первый не содержит условий выборки по полу и возрасту, второй делает требуемую выборку, но кроме этого мне необходимо знать кол-во всех существующих свойств у выбранных номеров.

 

[mani13]

[удалено, так как медленно]

а чем не нравится твой 2ой вариант?

 

[slach]

в два запроса делать не судьба ???

сначала выбрал number по условию
потом выбрал по number IN

в принципе на MySQL 4.1.x можно попробовать следующее

SELECT t1.number, 
            COUNT(DISTINCT t1.property) AS cnt_property 
FROM properties AS t1 
WHERE t1.number IN (
           SELECT t2.number, COUNT(t2.property) AS  has2property
           FROM properties AS t2 
           WHERE 
              (t2.property = 'sex' AND value = 'm')
              OR
              (property = 'age' AND value = '29')
          GROUP BY t2.number
          HAVING has2property>=2
)
GROUP BY t1.number 
ORDER BY cnt_property

да кстати, про 90 секунд
а СКОЛЬКО у тебя строк в таблице ??

все поля ПО ОТДЕЛЬНОСТИ проиндексированы ?
PRIMARY KEY (number,property,value)
стоит ? чтобы одинаковых записей лишних не было

 

[Red Hat]

Да я уже остановился уже на своем втором варианте. Чем не устраивает? Выглядит просто тяжеловато...

slach - По поводу двух запросов... Нет такое точно нельзя. База большая, я привел частный запрос, а может быть запрос по выбору всех особей женского пола с возрастом 5-45 лет, например, а это десятки тысяч... И как я потом найду среди них у кого максимальное кол-во доступных свойств.....
Тоже и про индексацию, значительно это базу утяжелит... Там в таблице еще куча полей и есть связи с другими таблицами которые влияют на текущее состояние подписчика...

slach - твой запрос не прошел, #1241 - Operand should contain 1 column(s)
Даже копаться не хочу - баг мускула или в запросе косяк - очень много времени на 1 запрос потратил уже...

Спасибо, короче, вопрос снимаю.

И вообще летом решил на RH AS 4 + Ora10 мигрировать)))

 

[chira]

такое не пойдёт:

SELECT p1.number, COUNT(*) cn
FROM properties p1
  INNER JOIN p2 ON p1.number=p2.number AND p2.property = 'sex' AND p2.value = 'm'
  INNER JOIN p3 ON p1.number=p3.number AND p3.property = 'age' AND p3.value = '29'
GROUP BY 1
ORDER BY cn DESC

 

[alexhemp]

Поищи по форуму я давал решение одним запросом на выборку по нескольким свойствам.

Грубо - сперва делаешь пересечение - объекты-свойства. Потом отрезаешь те свойства что не подходят, потом группируешь по объектам считая количество совпавших свойств (HAVING count(id_объекта) = 2) в твоем случае.

 

[Olimp]

А может проще изменить структуру таблицы?
Id
sex
age
nick
height

в итоге проблем будет меньше

 

[slach]

Olimp не советуй ЕРУНДЫ
у него правильная структура
у него индексов нет

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

вот он ИДИОТИЗМ и вскрылся ))

чем больше СВЯЗЕЙ с другими таблицами тем больше индексов должно быть

если у тебя есть выборка WHERE field='value'
нужен индекс по field

тебе место на винте жалко под индексные данные ?

у тебя INSERT'ы массовые в эту таблицу ПОТОКОМ идут??
по моему НЕТ
даже если потоком
то не часто, и вообще про DELAY_KEY_WRITE слышал ?
почитай
http://dev.mysql.com/doc/mysql/en/create-table.html

ИНДЕКСЫ не могут _утяжелить базу_
они могут увеличить накладные расходы на INSERT\UPDATE не более того

нет я конечно понимаю
база может весить целый гигабайт
и ждать пока сделается ALTER TABLE ADD INDEX долго =)
но это лучше, чем сидеть и "тратить много времени" на один запрос

 

[tashkentchi]

Red Hat

SELECT number, COUNT(*) AS countproperty, SUM((property = 'sex' AND value = 'm') OR (property = 'age' AND value = '29')) AS needed FROM properties GROUP BY number HAVING needed > 1 ORDER BY countproperty

 

[Red Hat]

Olimp - структуру менять нельзя
Свойства могут быть разные. Их сами пользователи создают. Создали чат-room FLIRT (сгенерировалось новое свойство), кто подписался тому оно навесилось.

slach - Про индексы, есессно они ЕСТЬ. Они не могут не быть, но предусмотреть все возможные ходы 'проблематично'. В базе под 200 таблиц и она постоянно расширяется. Да в properties записей потоками нет, но есть таблицы на которые очень большая нагрузка. Рассмотрена была частная задача и весьма упрощенная. На самом деле существует набор таблиц `properties(channel1)` `properties(channel2)` `properties(channel3)`..... Человек может иметь регистрационные данные одновременно в нескольких таблицах и какие-то каналы могут быть организационно объединены на основе других таблиц. Цель этого запроса - это только 1 шаг в вычислении рейтинга подписчика: чем больше анкетных данных - тем выше начальный кредит доверия. Но кроме этого есть другие критерии: кол-во сообщений в системе за месяц (общительность), дата последнего сообщения.... итд ипр - в других таблицах... На все это навесить индексы я ни за какие деньги не возьмусь...))) С учетом того, что уже на данный момент есть 7 параллельно живущих pl-скриптов (+2 потенциально живущих))))), каждый из которых по своему использует данные + набор php-скриптов... (это какие индексы должны быть) Вообще мое мнение - поддержка индексов довольно дорогое удовольствие для сервера + действительно индексы замедляют DML операции (update, delete, insert..) Поэтому в моем случае я создал несколько основных индексов только с высокой селективностью PRIMARY и UNIQUE KEYS. Да и вообще в идеале каждая таблица должна иметь один уникальный ключ PRIMARY KEY (это ограничение целостности или constraint) который обеспечивает уникальность всей строки а также гарантирует что его значения не будут NULL и не будут повторяться. )))))
Кстати, DELAY_KEY_WRITE только для MyISAM.

В общем всем спасибо, а запрос от tashkentchi я учту, он рабочий...