MySQL - numer porządkowy

0

Tabela x zawierająca multum rekordów należących do różnych użytkowników. Kolumna ordering zawierająca numer porządkowy (indywidualny dla każdego usera).

Sytuacja A) User dodaje rekord wskazując pod jakim nr porządkowym ma się znaleźć w bazie.
Sytuacja B) User lub admin aktualizuje rekord zmieniając jego nr porządkowy i / lub user_id
Sytuacja C) User usuwa rekord powodując powstanie luki w numeracji

Przy dużej ilości rekordów nie możemy sobie pozwolić na aktualizację każdego numeru osobno, trzeba to robić globalnie.
Dodając rekord możemy wykonać zapytanie typu 'UPDATE x SET ordering = ordering + 1 WHERE ordering >= ''.$t_data['ordering'].''' AND user_id = ''.$user_id.'''; i po tym zapytaniu dodać nowy rekord. W ten sposób przesuwamy o jedno oczko wszystkie rekordy powyżej.
Usuwając rekord wykonujemy zapytanie 'UPDATE x SET ordering = ordering - 1 WHERE ordering >= ''.$t_data['ordering'].''' AND user_id = ''.$user_id.'''; Czyli zmniejszamy o jedno oczko powyżej powstałej luki.

Aktualizując rekord robi się już troszkę grubiej.

Jedno zapytanie doda 1 oczko do niektórych rekordów w przypadku gdy nr porządkowy zostaje zmniejszony
Drugie zapytanie odejmie 1 oczko od niektórych rekordów w przypadku gdy nr porządkowy zostaje zwiększony.

Schody zaczynają się wtedy kiedy administrator zmienia user id. Wówczas dzieje się to tak jakby z usera pierwszego rekord usuwano, a do usera drugiego dodawano.


Ogarnąłem to działa to nawet wydajnie, ale pytanie brzmi.

Czy istnieje jakiś wewnętrzny mechanim mysql pozwalający na błyskawiczną renumerację rekordów w danej tabeli w przypadku zmiany numeru itp.?

1

jeśli po usunięciu rekordu chcesz przenumerować tak aby nie było dziury to znaczy, że te numery nic nie oznaczają (nie identyfikują rekordu w żaden sposób). W takim wypadku powinny być generowane podczas wyświetlania danych a nie zapisywania w bazie. Jeśli np. jest to kolejność wyświetlania to jej nie przeszkadzają "dziury" w numeracji.Jeśli jest to natomiast unikalne ID to jego ruszać nie wolno. Sprecyzuj problem bo mam wrażenie, że próbujesz coś robić na około.

0

Niestety w tym przypadku dziury przeszkadzają. Serwis jest tak skonstruowany, że użytkownik ma możliwość określenia w jakiej kolejności mają wyświetlać się artykuły na stronie. Może wybrać według daty (malejąco rosnąco) lub według ręcznie nadanej kolejności.

Jeśli wybierze ręcznie nadawaną kolejność wówczas w formularzu dodawania / edycji artykuły ma możliwość określenia na której pozycji rekord ma się wyświetlać.

I teraz jeśli w bazie jest 10 artykułów NIE MOŻE być takiej sytuacji, że user wchodzi do edycji artykułu i widzi pozycję 11 (nie może bo według mnie może to wprowadzać w błąd). Dlatego chcę pozostawić możliwość ręcznego decydowania o kolejności, przy jednoczesnym zachowaniu spójności ergonomicznej serwisu.

Przy czym jest to napisane na tyle inteligentnie, że jeśli w edycji artykułu user nie zmienił numeru porządkowego, wówczas zapytania dot. update'u kolejności nie są niepotrzebnie wykonywane. Z tego powodu szukam wydajniejszej możliwości rozwiązania tego problemu. Choć mam wrażenie, że wydajniej niż to co wymyśliłem się nie bardzo da, bo MySQL nie posiada dedykowanych narzędzi do tego typu operacji (bo są nietypowe).

0

Przecież możesz wyliczać numer pozycji dla danego użytkownika w ramach zapytania i korzystać z orderingu tylko dla uporządkowania rekordów.
W ten sposób dziury po stronie bazy nie będą dla użytkownika widoczne.

Coś na zasadzie:

SELECT 
	t.*,
	@rownum := @rownum + 1 AS row_number
FROM 
	TABELA t, 
	(SELECT @rownum := 0) r
WHERE t.userid=:userid
ORDER BY
	ordering;

albo z wykorzystaniem funkcji analitycznych:

SELECT 
	t.*,
	ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordering) as row_num
FROM 
	TABELA t
;
0

No właśnie to co wymyśliłem opiera się na Twoim pierwszym przykładzie. Dodałem wątek, bo zacząłem się zastanawiać, czy nie ma wydajniejszych rozwiązań.

0

Ale czekaj. Bo Ty mówisz o select. Ja podobne rozwiązanie zastosowałem dla update.

Twój przykład ma pewną wadę. Jak wchodzisz do edycji rekordu, gdzie ma być wyświetlona pozycja konkretnego rekordu musiałbyś niepotrzebnie wykonywać zapytanie globalne, co dla dużej ilości rekordów jest mało wydajne. Lepiej zrobic raz mało wydajny update niż zaprzęgać do select tworzenie wirtualncyh wartosci.

0

Szczerze powiedziawszy nie rozumiem tej funkcjonalności. Jako użytkownik wyobrażałbym sobie GUI, w którym mogę przesunąć (drag&drop) na wybraną pozycję, a nie, że jakieś numery porządkowe mam uzupełniać :-)

Nie wiem ile dla Ciebie to jest dużo rekordów? 1k, 10k, 100k, ... ? I jak często użytkownik/admin wykonuje takie zmiany i ile one powinny trwać by była spełniony warunek "jest wydajnie"?

Można też inaczej myśleć o takich rekordach i przechowywać dwa dodatkowe atrybuty: prev_element, next_element i je aktualizować dla rekordów powiązanych, wówczas przy przesunięciu elementu na jakąś pozycję aktualizowałbyś max. 5 rekordów:

  1. Rekord wskazywany przez current.PREV
  2. Rekord wskazywany przez current.NEXT
  3. Rekord current
  4. Rekordy pomiędzy które wstawiasz element current (w skrajny przypadku były to 1 rekord jak przesuwasz na początek/koniec)

Do tego zapytanie hierarchiczne i masz ustalony porządek.

0

To jest tak. Masz system artykułów. Jedna tabela, która skupia w sobie artykuły z kilkudziesięciu działów. Czysto teoretycznie może być na tym oparty większy serwis informacyjny. 100 artykułów dziennie daje 36 600 artykułów rocznie. Liczba jest trochę na wyrost, ale pokazuje, że nie mówimy o milionach rekordów, ale na tyle dużej ich ilości, że wydajność zapytań ma tu już znaczenie. Po 5 latach będzie ich może ze 150 000 max. Z racji tego, że treści są w kolumnie typu BIGTEXT fizycznie są przechowywane poza tabelą, więc tabela nie będzie duża (pod kątem sortowania itp.).

Funkcjonalność ma być na tyle uniwersalna, by obsłużyć małą wizytówkę jak również duży portal informacyjny.

Problem z jakim notorycznie spotykam się przy małych realizacjach: sortowanie według daty jest niewystarczające, klient dodał artykuł i nie chce by ten artykuł pojawiał się jako pierwszy na liście. Chce mieć możliwość samodzielnego wskazywania pozycji artykułu. Bywa, że artykuł jest subdziałem głównej podstrony. Np. masz podstronę O NAS i w niej 10 tekstów charakteryzujących firmę.

Stąd pomysł na numer porządkowy, który to jednak jest całkowicie bezużyteczny przy dziale, w którym tych artykułów będzie multum (wiadomo, że nikt nie ogarnie kolejności dla 500 artykułów).

Jeśli w ustawieniach danego działu klient ustawi sobie kolejność po dacie dodania, opcja nadawania numeru porządkowego nie będzie dostępna w formularzu dodawania artykułów do tego działu.

Jeśli jednak jakiś nieogarnięty user, włączy opcje nadawania ręcznej kolejności i będzie dodawał multum artykułów za każdym razem podając numer 1, każde dodanie artykułu będzie wiązało się z koniecznością przeliczenia numerów pozostałych artykułów.

Tu bardziej chodzi o zabezpieczenie się przed nierozgarniętym userem niż stworzenie funkcjonalności istotnej z punktu widzenia logiki działania. Ta kolejność to jedna z opcji, która powinan być wybierana tylko w określonych sytuacjach.

0
webkam napisał(a):

Z tego co piszesz:

  • 500+ artykułów -> ręczne ustawianie pozycji bezużyteczne
  • 100 artykułów dziennie -> czyli 100 update dziennie?
  • <500 -> 500 / 36500 (w skali roku) - 0,01369 , czyi jakieś 1,4% danych jest dotkniętych update

Update po indeksie powinien być mega szybki.

1 użytkowników online, w tym zalogowanych: 0, gości: 1