There is a query, displayed on a page grid, controlled by limit. When adding a new row to the table participating in the query, you need to display a list in this table, starting with a new record. Record I can identify by unique identifier. I need to find the values ​​start, end (limit start, end) where the required string will be located in the end-start range.

    3 answers 3

    @KiTE gave you the correct answer, how to do it using SQL.

    But, generally speaking, access to MySQL indexes at a low level is, and for a long time already. True, there is little to use it.

    Details read on MySQL Handler Syntax .

    Here is an example. I created the following table:

    mysql> SELECT * FROM test; +---+---------+ | a | b | +---+---------+ | 1 | alpha | | 2 | bravo | | 3 | charlie | | 4 | delta | | 5 | echo | | 6 | foxtrot | +---+---------+

    A field b created an index, also with the name b ;

    Now open the handler:

     mysql> HANDLER test OPEN;

    And read directly. Attention , here and hereinafter b is the name of the index, not the field (damn, it was necessary to call it another way).

    We are looking for the desired entry:

     mysql> HANDLER test READ b = ('delta'); +---+-------+ | a | b | +---+-------+ | 4 | delta | +---+-------+

    We read two records before it, in the order of the index:

     mysql> HANDLER test READ b PREV LIMIT 2; +---+---------+ | a | b | +---+---------+ | 3 | charlie | | 2 | bravo | +---+---------+

    The cursor (reading position), by the way, has shifted. We are now not on 'delta', but on 'bravo'. We read the following four entries:

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

    Played, and enough:

     mysql> HANDLER test CLOSE;

    Here is a mechanism. You can use comparisons, WHERE can. It works much faster than SELECT.

      If the key is auto-incremental, then the position of the new row in the table can be recognized by the query:

       SELECT COUNT(*) FROM table_name WHERE id < $id_value

      He will count the number of previous records. This value can be taken as the position of the row in the table (starting with 0).

      Knowing the number of records on the grid page, and the position of the record in the base table, you can calculate the initial position of the page on which the required record should be.

       $top_position = floor($row_posotion / $page_row_count)

      Then you can generate a query:

       SELECT * FROM table_name LIMIT $top_position, $page_row_count

      UPD: If sorting by columns is used, then at the beginning, you need to find out the values ​​of these columns for the record you are looking for, then count the number of previous records. And continue to display the page.

      For example, if you sort by the principle of ORDER BY filed1, field2, field3 DESC , then:

       -- Получаем значение сортируемых столбцов для искомой записи 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)

      And, further, knowing the position:

       SELECT * FROM table_name ORDER BY filed1, field2, field3 DESC LIMIT $top_position, $page_row_count

      How to get $top_position , knowing $row_posotion ( COUNT(*) - 1 ), see above.

      • This does not work for the following reasons: 1. We do not know whether the records were deleted from the table (for example, identifiers such 1,2,3,7,8,9,11 could be deleted — it looks like the records 4,5,6 were deleted, ten). There may be another order. 2. If the query has a sort or other composite tables (we sort by non-identifier). a not-so-right solution comes to mind and we can say stupid in the forehead: select rn from (select rownumber as rn, id, other fields ... [our query where we put numbers in the query in order) to rows as d where d.id =: pnID - org
      • On the 1st point - everything is fine. COUNT(*) count the number of actual records. And if sorting is used, then you need to conjure with the query condition to get COUNT(*) . - KiTE
      • Unsuccessfully first item described. A classic case for a single table and your answer is really correct. Forget) I wanted to convey something else, I would use the internal search mechanism for indexes without using complex queries. Yes, it’s still not a table with us, but a whole query, which one is unknown, but having an identifier (for example). - org
      • Now I do not even know. It seems to me because of my incorrect statement of the question, we went away. Using your method, you have to write a query creation wizard for defining .positions. It would be necessary for something more universal. - org
      • As far as I know, MySQL does not have the ability to directly access indexes. They are used by the query optimizer engine. And the developer creates them based on the conditions in the requests. - KiTE

      You can number lines in a query with the following two queries:

       SET @n :=0; SELECT @n := @n +1 AS num, field1, field2, fieldN FROM table;
      • set @n: = 0; SELECT num FROM (select @n: = @ n + 1 AS num, id from table where ...) d where d.id =: pnID Excites how such queries will behave on large tables !? Therefore, I want an even simpler option. - org
      • Probably a temporary table will be created. Need to watch EXPLAIN on real data. - Ilya Pirogov
      • In terms of performance, it will work quickly enough. But in your situation will not help. Records of the current sample are numbered. All the same, you need to calculate the starting position. - KiTE 1:56 pm