Форум MySQL

Есть таблица с пользователями,

`user_id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(25) DEFAULT NULL,   `last_time` int(11) unsigned DEFAULT '0',

из которой происходит выборка и которая отсортирована, допустим по полю last_time

SELECT * FROM users ORDER BY last_time DESC

В полученной выборке надо определить порядковый номер пользователя с определённым user_id.

Как это оптимальнее сделать? Неужели придётся вытаскивать полную выборку и средствами php отсчитывать позицию? Но, если пользователей за сотню тысяч, то этот метод вряд ли подойдёт.

PS. Пример элементарный. В реале выборка и сортировка несколько сложнее, с использованием COALESCE

Я бы создал таблицу из двух столбцов - автоинкремента и userid.
И заполнил бы её срезом из этого запроса. П
Потом получил рейтинг по userid'у.
Потом строки удалил бы.

Не понял :)

Что такое срез? Можно поподробнее про идею рассказать, если не сложно.

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

INSERT INTO ratings (userid)  SELECT userid FROM tbl ORDER BY COALESCE(...)

далее берем mysql_insert_id() и mysql_affected_rows()
далее

SELECT id FROM ratings WHERE userid = $userid

далее

DELETE FROM ratings where id BETWEEN ... AND ...

В MySQL, насколько мне известно прямых инструментов подсчета рейтинга нет.
И поэтому приходится так извращаться.

Т.е. если я правильно понял ratings хранит текущий "слепок" списка пользователей? И операцию INSERT надо проводить каждый раз, когда необходимо вывести номер позиции пользователя?

Или я неправильно понял?

Да. И операцию DELETE. (Или TRUNCATE)
Вытаскивать таблицу к клиенту необходимости нет.
Но заполнять- увы.

Если в качестве ratings использовать временную таблицу, то может быстро получиться.
Но с увеличением объёма таблицы может памяти нехватить на такие масштабные действия.

Но всё равно спасибо, буду экспериментировать.

А еще можно попробовать поразвлечься с курсорами.
Читать курсором SELECT, пока не встретится нужный userid

Хм... курсорами пока не интересовался.

А как у них со скоростью и с жадностью к ресурсам? На вскидку какой вариант получится быстрее этот или в INSERT/DELETE ?

По-моему, INSERT будет быстрее.

Достоверно ответит только эксперимент.

В полученной выборке надо определить порядковый номер пользователя с определённым user_id.
т.е. из сотни тысяч избранных нужно определить одного суперизбранного и его id?
по какому принципу определяется суперизбранный?
или же всё же в запросе будет LIMIT ? и суперизбранных будет несколько
Объясните суть этой затеи, что в конечном итоге должно "улететь" в браузер?

Суть - показать юзеру его позицию в общем списке пользователей.

как-то так?

SELECT u.name, u.last_time, (   SELECT COUNT(1) FROM users uc   WHERE uc.last_time < u.last_time ) + 1 FROM users u ORDER BY u.last_time DESC

Спасибо, но такие запросы я умею составлять.

Засада в том, что пользователей 100000+ и запрос надо проводить несколько раз в минуту.

Мне хотелось бы услышать идею о максимально быстром варианте решения такой задачи.

Сами запросы можно не писать, можно просто в виде идеи.

ну а в чём проблема? результатом выборки должна быть таблица в которой последний должен идти искомый юзер и COUNT от общего числа выбранных. И нафик не нужен никакой id

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

Для облегчения понимания о каком типе выборки идёт речь:

http://mamba.ru/search.phtml?offset=0&s_l=N&s_i=N&s_f=&s_t=&s_tg=&s_c=3159_0_0&s_m=0&s_p=checked&s_w=&s_ty=All&s_vf=&s_vw=&s_vip=&s_v=&search_mode=Base&gid=63247731

Задача: показать любому пользователю из такого альбома его порядковый номер.

А если хранить где-нибудь n - число удалённых юзеров? то порядковый номер будет id-n
как вариант? сурово и быстро

про сортировку не забывайте. Она не по id идёт.

а по чём? последнее посещение или дата регистрации?

По дате регистрации и по специальному флагу, который кидает людей, оплативших определённую услугу наверх списка.

Так это же совсем другие двое!
Здесь можно безо всякой таблицы парой-тройкой селектов обойтись.
Минутку.

$vip  = my1("SELECT vip FROM USERS WHERE userid = $id"); $bias = $vip ? 0 : my1("SELECT COUNT(*) FROM users WHERE vip"); $rating = $bias + my1("SELECT COUNT(*) FROM users WHERE $vip = vip AND id < $id ");

Понятно, что составной индекс на полях vip, id не помешает.
Если флаг хранится в сессии, то первый запрос не требуется.

Не совсем правильно выразился. Это скорее не флаг

Вот запрос (убрал не нужные для понимания поля и присоединяемые таблицы):

SELECT t1.name, bla bla , COALESCE(t4.date, t1.sort) as sortdate FROM user_general t1 LEFT JOIN user_up t4 ON (t4.user_id = t1.user_id AND t4.mode = ?i) ORDER BY IF(t1.photos >= 1,0,1),sortdate

user_up имеет вид:

`user_id` int(11) DEFAULT NULL,   `date` int(11) DEFAULT NULL,   `mode` tinyint(1) unsigned DEFAULT '0',

т.е. когда юзер оплачивает поднятие своей анкеты наверх, то его ID вместе с временем и кодом операции (mode = 1 - поднятие по всему альбому, mode = 2 поднятие в выборке по стране, mode = 3 поднятие в выборке по штату и т.д.) записывается в таблицу user_up. А потом эта таблица присоединяется к выборке, чтобы построить и отсортировать список.

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

Ну или в отдельной таблице ratings (userid, rate)

>Ну или в отдельной таблице ratings (userid, rate)

(слегка подумав) Пожалуй, это будет самый оптимальный вариант.

По скорости выборки, думаю да. А вот по построению... Очень легко будет случайно нарушить целостность и сбить весь порядок.

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

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

При добавлении нового пользователя опять надо смотреть куда его впихнуть, так как если наверху имеются "платники" и/или пользователь без фотки, то его надо запихивать куда-то в глубину списка.

В общем проблем прилично :(

>По скорости выборки, думаю да. А вот по построению... Очень легко будет случайно нарушить целостность и сбить весь порядок.

>Ведь если юзер из середины таблицы поднимается наверх, то надо всем кто до него стоял прибавлять единицу, а у всех кто после него единицу отнимать.

У всех кто после - не надо.
У всах кто до - надо.
Но делается это одним запросом.
Ну двумя.


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

LOCK TABLES ; SELECT rate FROM ratings WHERE id = $id ; // определение $max  UPDATE ratings  SET rate = IF(rate = $max; $min; rate+1) WHERE   rate BETWEEN $min AND $max ; UNLOCK TABLES ;

>
>При добавлении нового пользователя опять надо

Той же процедурой.

Огромная просьба "мелочи" подобного рода указывать в самом начале ;)