Как найти порядковый номер в бд?

Question

[michellie]

Есть БД в mysql которой тысячи таблиц, в каждой сотни тысяч записей, выводятся сортируясь по рейтингу

SELECT * FROM `top_elements` ORDER BY `rating` DESC LIMIT {$start}, {$conf['pp']}

проблемы начинаются при поисковом запросе, когда нужно выдернуть элемент по 'title', тк он может находиться на 800 000 месте, но при этом я знаю его id сразу, сейчас это происходит вот так:

$pos = mysql_result(mysql_query("call find('Женя')"),0);
echo "Женя на $pos месте!";

DELIMITER |
DROP PROCEDURE IF EXISTS find|
CREATE PROCEDURE find (IN v varchar(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE n INT(11) DEFAULT 0;
    DECLARE name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT `username` FROM `table` ORDER BY `message` DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
    OPEN cur;
 
    read_loop: LOOP
        FETCH cur INTO name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET n = n + 1;
        IF name = v THEN
            SELECT n;
            LEAVE read_loop;
        END IF;
    END LOOP;
 
    CLOSE cur;
END|
DELIMITER ;

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

Answer 1

[michellie]

В общем, самый жизнеспособный способ оказался через таблицу с позициями , транзакции и крон.

include 'bdconnect.php';
mysqli_begin_transaction($link, MYSQLI_TRANS_START_READ_WRITE);
mysqli_query($link,"TRUNCATE TABLE top_pos;");
mysqli_query($link,"SET @position_number := 0;");
mysqli_query($link,"SET @rating_id := '';");
mysqli_query($link,"INSERT INTO
  top_pos (element_id, `position`, rating_id)
SELECT
  id_elem,
  IF(@rating_id = e.id_top, @position_number := @position_number + 1, @position_number := 1) AS position_number,
  @rating_id := id_top AS rating_id
FROM
  top_elements AS e
ORDER BY
  e.id_top,
  e.rating DESC,
  e.id_elem;");
mysqli_query($link,"SET @position_number := 0;");
mysqli_query($link,"SET @rating_id := '';");
mysqli_query($link,"UPDATE
  top_elements AS e
  INNER JOIN
  (SELECT
    e.id_elem,
    IF(@rating_id = e.id_top, @position_number := @position_number + 1, @position_number := 1) AS position_number,
    @rating_id := id_top AS rating_id
  FROM
    top_elements AS e
  ORDER BY
    e.id_top,
    e.rating DESC,
    e.id_elem) AS t ON t.id_elem = e.id_elem
SET
  e.position = t.position_number;");
mysqli_commit($link);
mysqli_close($link);

Answer 2

[Stalker_RED]

SELECT * FROM `top_elements` WHERE id=800123

Comments

[michellie]

Мне не элемент надо найти а его порядковый номер при определенной сортировке, не пробегая циклами по всем записям в таблице

[Stalker_RED]

michellie, что-то вроде эмуляции ROW_NUMBER()?

Answer 3

[Boris Köln]

Выкиньте весь этот говнокод и увольте индуса, которые это написал.

Все это делается в 4 строки:

-- выполнять по cron или trigger
DROP TABLE IF EXISTS my_table_pos;
CREATE TABLE my_table_pos (id int, pos int NOT NULL AUTO_INCREMENT, KEY `my_table_pos_id` (`id`));
INSERT INTO id_pos (id) SELECT id FROM my_table ORDER BY my_sort;


SELECT my_table_pos.pos FROM my_table, my_table_pos WHERE my_table.id = my_table_pos.id AND my_table.username = 'Женя';