There is a mysql table with entries that are displayed via php on the page in a certain order, for example: 128, 59, 70, 293, 19, etc. Each new record should take a certain place in the given order (the order of records I define myself depending on the content). For example, the new record 476 should be inserted between 59 and 70, i.e. such an array is formed.

Now I do it this way: I give a certain number to each record and in the request I specify the sorting by these numbers. But I think this algorithm is not very effective, especially since I prescribe these numbers manually.

Any idea how you can do this in a normal way without heavily loading the server?

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

In fact, a separate field with identifiers specifying the order is not such a bad idea. The field is numeric, so the sorting by it is very fast, it can be indexed, so the query can be further accelerated and practically does not depend on speed on the size of the table.

It is not necessary to fill this field manually; you can fasten an interface that allows you to drag positions to the user and recalculate this field automatically.

There are not so many alternatives and they are no longer so effective. For example, the MySQL function FIELD() , which returns the position of the occurrence of the first argument in the list, which is given by the remaining arguments.

As the first parameter, the id field is passed, and the list of 128, 59, 70, 293, 19 is used as the remaining arguments. Since all non-listed values ​​will get the value 0, it is better to replace it with 65536

 SELECT * FROM tbl ORDER BY IF(FIELD(id, 128, 59, 70, 293, 19) = 0, 65536, FIELD(id, 128, 59, 70, 293, 19))

or alternatively, you can reverse the list and use the DESC keyword, then the values ​​0, returned by the FIELD() functions will line up and can be sorted by some other field

 SELECT * FROM tbl ORDER BY FIELD(id, 19, 293, 70, 59, 128) DESC, name;

However, this approach is worse than the additional field, since position calculations are performed when each row is sampled, such a dynamic field cannot be indexed (at least in MySQL).

It is better to leave a separate field for sorting, but consider the possibility of its automatic filling. For example, by default, assign it a maximum value of + 1, i.e. place at the end, and when dragging an element, calculate the offset and modify ( UPDATE ) this field for all rows in the offset interval.