MySQL - определение позиции в запросе или как использовать механизм поиска по индексам btree

#php #mysql #pagination

Есть запрос, выводимый на постраничном гриде, управляемый limit. При добавлении новой
строки в таблицу, участвующей в запросе нужно отобразить список в данной таблице, начиная
с новой записи. Запись я могу идентифицировать по уникальному идентификатору. Мне нужно
найти значения start, end (limit start, end) где в диапазоне end-start будет находится
искомая строка.
    

Ответы

Ответ 1

@KiTE дал вам правильный ответ, как это сделать средствами SQL.
Но, вообще говоря, доступ к индексам MySQL на низком уровне есть, и давно уже. Правда,
мало когда нужно им пользоваться.
Подробности читать на MySQL Handler Syntax.
А вот пример. Я создал такую таблицу:
mysql> SELECT * FROM test;
+---+---------+
| a | b       |
+---+---------+
| 1 | alpha   |
| 2 | bravo   |
| 3 | charlie |
| 4 | delta   |
| 5 | echo    |
| 6 | foxtrot |
+---+---------+

По полю b создал индекс, тоже с именем b;
Теперь открываем хандлер:
mysql> HANDLER test OPEN;

И читаем напрямую. Внимание, здесь и далее b — это имя индекса, а не поля (блин,
надо было по-другому обозвать).
Ищем нужную запись:
mysql> HANDLER test READ b = ('delta');
+---+-------+
| a | b     |
+---+-------+
| 4 | delta |
+---+-------+

Читаем две записи перед ней, в порядке индекса:
mysql> HANDLER test READ b PREV LIMIT 2;
+---+---------+
| a | b       |
+---+---------+
| 3 | charlie |
| 2 | bravo   |
+---+---------+

Курсор (позиция чтения), кстати, сместился. Мы теперь не на 'delta', а на 'bravo'.
Читаем следующие четыре записи:  

mysql> HANDLER test READ b NEXT LIMIT 4;
+---+---------+
| a | b       |
+---+---------+
| 3 | charlie |
| 4 | delta   |
| 5 | echo    |
| 6 | foxtrot |
+---+---------+

Поигрались, и хватит:
mysql> HANDLER test CLOSE;

Вот такой механизм. Можно использовать сравнения, можно WHERE. Работает заметно быстрее,
чем SELECT.


Ответ 2

Если ключ автоинкрементальный, то позицию новой строки в таблице можно узнать запросом:
SELECT COUNT(*) FROM table_name WHERE id < $id_value

Он посчитает кол-во предыдущих записей. Это значение можно принять за позицию строки
в таблице (начиная с 0).
Зная кол-во записей на страницу грида, и позицию записи в таблице базы, можно рассчитать
начальную позицию страницы на которой должна находиться искомая запись.
$top_position = floor($row_posotion / $page_row_count)

Дальше можно генерить запрос:
SELECT * FROM table_name LIMIT $top_position, $page_row_count

UPD:
Если используется сортировка по столбцам, то, в начале, нужно узнать значения этих
столбцов для искомой записи, затем, посчитать кол-во предыдущих записей. И дальше выводить
страницу.
Например, если сортировать по принципу ORDER BY filed1, field2, field3 DESC, то:
-- Получаем значение сортируемых столбцов для искомой записи
SELECT filed1, field2, field3
FROM table_name
WHERE id = $id_value
INTO @filed1, @field2, @field3;
-- Получаем кол-во предыдущих записей в отсортированном списке до искомой записи
SELECT COUNT(*) - 1 /* -1 потому что искомая запись тоже попадает в COUNT(*) */
FROM table_name
WHERE
    (filed1 <= @filed1) AND
    (filed2 <= @filed2) AND
    (filed3 >= @filed3)

И, дальше, зная позицию:
SELECT *
FROM table_name
ORDER BY  filed1, field2, field3 DESC
LIMIT $top_position, $page_row_count

Как получить $top_position, зная $row_posotion (COUNT(*) - 1), смотри выше.


Ответ 3

Пронумеровать строки в запросе можно следующими двумя запросами:
SET @n:=0;
SELECT @n:=@n+1 AS num, field1, field2, fieldN FROM table;