ユーザーが画面上からDB上のデータの並び替えをする際、効率的な実装方法は？

質問

EC サイトを構築しており、
店舗管理者が商品の並びを自由に替えるようにしたいです。

例えば、以下のようなテーブルが DB 内にあるとします。

+---+-------+
|ID | name　|
+---+-------+
| 1 | 商品1 |
| 2 | 商品2 |
| 3 | 商品3 |
| 4 | 商品4 |
| 5 | 商品5 |
+---+-------+

ユーザーが
商品1 - 商品4 - 商品2 - 商品3 - 商品5
という順で並べ替えを行う機能を実装するには
以下の 2 つの方法が考えられます。

【方法1】

rank というカラムを追加して

+---+-------+------+　　+---+-------+------+
|ID | name　| rank |　　|ID | name　| rank |
+---+-------+------+　　+---+-------+------+
| 1 | 商品1 |　　1 |　　| 1 | 商品1 |　　1 |
| 2 | 商品2 |　　2 | => | 2 | 商品2 |　　3 |
| 3 | 商品3 |　　3 |　　| 3 | 商品3 |　　4 |
| 4 | 商品4 |　　4 |　　| 4 | 商品4 |　　2 |
| 5 | 商品5 |　　5 |　　| 5 | 商品5 |　　5 |
+---+-------+------+　　+---+-------+------+

と rank を更新し、rank の昇順で表示させる

【方法2】

next_id というカラムを追加して

+---+-------+---------+　　+---+-------+---------+
|ID | name　| next_Id |　　|ID | name　| next_id |
+---+-------+---------+　　+---+-------+---------+
| 1 | 商品1 |　　　 2 |　　| 1 | 商品1 |　　　 4 |
| 2 | 商品2 |　　　 3 | => | 2 | 商品2 |　　　 3 |
| 3 | 商品3 |　　　 4 |　　| 3 | 商品3 |　　　 5 |
| 4 | 商品4 |　　　 5 |　　| 4 | 商品4 |　　　 2 |
| 5 | 商品5 |　　　　 |　　| 5 | 商品5 |　　　　 |
+---+-------+---------+　　+---+-------+---------+

と next_id を更新し、
ID と next_id を交互に見ながら画面に並べる

【方法2】では、どんなにデータ数が多くなっても
3 レコードだけ更新すれば済みますが
【方法1】だと、データ数が多くなれば
かなりの数のレコードを更新する必要があります。

ところが、
【方法1】だと SQL の order by rank でソートできるのに対し、
【方法2】だと全データを取得してからプログラムで並べ替えを行わなくてはならない
と思います。

そこで、【方法2】を使い、かつ SQL で簡単に取得できる方法はないか探しています。

【方法2】で、素直にプログラムで並べ替えればいいという意見もあるかと思いますが
ページ送りのある一覧画面で並べ替えもさせたいと思っており、
DB 側でで並べ替えられたデータを limit で 1 画面に表示する件数分だけ取得する
ということを行いたいのです。

もし、うまい方法があれば宜しくお願い致します。
DB は MySQL を使っていますが、他の DB にすることも可能で、
バージョンも問いません。

回答No.1

noname#259269

方法2はどう考えても、NGだと思うのですが。。。素直に方法1を取るべきです。

■理由

・SQL の order by だけで利用できる列と、プログラムを介在させないと利用できない列があった場合、前者の方が「データ」としての価値が高い(活用度が高い)。言い換えるとデータベースに格納しているデータなのに扱いが煩雑であることはマイナス。

・一度に並び替える個数が多くなった場合、方法2でも修正するデータ量が増えていく。つまり方法2としてのメリットが薄れる。

お礼

回答ありがとうございます。
もしかしたらエレガントなロジックが存在するのかなと思ったりもしたのですが、やっぱり素直に必要な数だけ更新する方がよさそうですね。
更新よりも参照の方が圧倒的にトラフィックは多いわけですし。