wiadomości - optymalizacja Opcje

[nospor]

Mam takie tabele

[SQL] pobierz, plaintext 
CREATE TABLE `message` (
  `ID` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `FK_MESSAGE` int UNSIGNED NOT NULL DEFAULT 0 comment ,
  `FK_LAST_MESSAGE` int UNSIGNED NOT NULL DEFAULT 0 comment 'Id ostatniej odpowiedzi. wskazuje na siebie gdy nie ma odpowiedzi',
  `MESSAGE` text NOT NULL,
  `FK_CUSER` int NOT NULL COMMENT 'Kto utworzyl',
  PRIMARY KEY  (`ID`),
  KEY `FK_MESSAGE` (`FK_MESSAGE`),
  KEY `FK_CUSER` (`FK_CUSER`),
  KEY `FK_LAST_MESSAGE` (`FK_LAST_MESSAGE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
CREATE TABLE `message_user` (
  `FK_USER` int UNSIGNED NOT NULL comment 'Id usera',
  `FK_MESSAGE` int UNSIGNED NOT NULL comment 'Id wiadomosci',
  PRIMARY KEY  (`FK_USER`,`FK_MESSAGE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
[SQL] pobierz, plaintext 

Mamy tabelę wiadomości oraz tabelę wiążącą, która mówi, że tę wiadomość może zobaczyć taki a taki user.
Wiadomość może też widziec ten, kto ją utworzył (FK_CUSER)

No i mam zapytanie pobierające wiadomosci

[SQL] pobierz, plaintext 
SELECT cm.ID, cm.MESSAGE,cm.IP,cm.CDATE,cm.COUNT_MESSAGES 
FROM message cm 
LEFT JOIN message_user cmu ON (cmu.FK_MESSAGE=cm.ID AND cmu.FK_USER=4)  
WHERE cm.FK_MESSAGE = 0 AND (cm.FK_CUSER = 4 OR cmu.FK_USER IS NOT NULL) 
ORDER BY cm.FK_LAST_MESSAGE DESC LIMIT 5
[SQL] pobierz, plaintext 

Czyli pobieram wiadomosci jakie może widziec użytkownik 4
W bazie mam 500tys rekordów. Wszystko smiga do czasu dodania OR cmu.FK_USER IS NOT NULL czyli sprawdzeniu tabeli wiążącej. EXPLAIN daje:

Kod

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    cm    index    FK_MESSAGE,FK_CUSER    FK_LAST_MESSAGE    4    \N    35    Using where
1    SIMPLE    cmu    eq_ref    PRIMARY    PRIMARY    8    cm.ID,const    1    Using where; Using index

Gdy dodam tego OR to zapytanie wykonuje się 2 sekundy. Idzie jakoś to przyspieszyc? Nie widzę za bardzo jakie mam tu dodać dodatkowe indeksy.


Przed chwilą wpadłem na pomysł, że wystarczy iż zamienie
cm.FK_CUSER = 4 OR cmu.FK_USER IS NOT NULL
na
cmu.FK_USER IS NOT NULL

czyli wywale sprawdzanie autora wiadomosci, a autora dodam do tabeli wiążącej. Wówczas zapytanie znowu śmiga. Wolałbym jednak tego rozwiązania nie stosować.

Powód edycji: [nospor]:

[BaN]

Nie za bardzo rozumiem

1. W zapytaniu używasz pól cm.CDATE, cm.COUNT_MESSAGES, a w definicji tabeli `message` brak takich pól. Zapytanie raczej nie powinno działać

2. Po co jest tabela `message_user`? Dane dotyczące identyfikatorów wiadomości i użytkownika są już w tabeli `message`

[nospor]

ad1) Hehe, wyciałem parę nic nie znaczących pol z tabeli. zapomniałem ich wyciąć w SELECT - potraktuj ze ich tam nie ma
ad2) message_user zawiera informacje do których użytkowników kierowana jest wiadomosc. W tabeli message nie ma takich informacji.

[BaN]

ad2) message_user zawiera informacje do których użytkowników kierowana jest wiadomosc. W tabeli message nie ma takich informacji.

Jak rozumiem przy zakładaniu wiadomości użytkownik wybiera do jakich użytkowników jest kierowana wiadomość, nie dyskutuję, bo to funkcjonalność, widocznie tak ma być

Mam dwie propozycje i jedną poprawkę:

1. założyć indeks podwójny lub nawet potrójny w tabeli `message`, nie wiem jakie masz pozostałe zapytania, więc trudno określić jakie indeksy będą najbardziej odpowiednie, ale minimalny indeks przynajmniej na polach (FK_MESSAGE, FK_LAST_MESSAGE), sam indeks na (FK_LAST_MESSAGE) chyba nie jest w ogóle potrzebny
2. zmienić odrobinę zapytanie, w zasadzie to poprawka kosmetyczna, nie powinno to przyśpieszyć zapytania, ale jest logiczne
   zamiast:

   [SQL] pobierz, plaintext 
   WHERE cm.FK_MESSAGE = 0 AND (cm.FK_CUSER = 4 OR cmu.FK_USER IS NOT NULL)
   [SQL] pobierz, plaintext 

   wpisać:

   [SQL] pobierz, plaintext 
   WHERE cm.FK_MESSAGE = 0 AND (cm.FK_CUSER = 4 OR cmu.FK_USER=4)
   [SQL] pobierz, plaintext 

   gdyż z wyniku złączenia uzyskasz albo cmu.FK_USER równe NULL albo 4
3. zmienić zapytanie nie na łączenie tabel, tylko skorzystać z warunku EXISTS:
   

   [SQL] pobierz, plaintext 
   SELECT ...
   FROM message cm
   WHERE cm.FK_MESSAGE = 0
     AND (cm.FK_CUSER = 4
       OR EXISTS (SELECT 1 FROM message_user cmu WHERE cmu.FK_MESSAGE=cm.ID AND cmu.FK_USER=4 LIMIT 1))
   ORDER BY cm.FK_LAST_MESSAGE DESC LIMIT 5
   [SQL] pobierz, plaintext 

Ten post edytował BaN 25.10.2010, 20:58:07

[nospor]

Jak rozumiem przy zakładaniu wiadomości użytkownik wybiera do jakich użytkowników jest kierowana wiadomość, nie dyskutuję, bo to funkcjonalność, widocznie tak ma być

Tak, tak ma byc

ad1) Bawiłem się już i taką kombinacją - nic to nie dało
ad2) Zrobiłem to celowo. Dzięki mojemu zastosowaniu unikam duplikacji głównego rekordu, przy kilku wpisach w tabeli wiążącej dla danej wiadomości
ad3) No niestety nic to nie dało. Mało tego, to rozwiązanie jest jeszcze gorsze, gdy zmula zapytanie nawet wówczas gdy wawalę z OR ten pierwszy warunek na FK_CUSER. Gdy zostawię joina zamiast EXISTS, to po wywaleniu FK_CUSER z OR zapytanie śmiga jak ta lala.

Kombinuj dalej. Chętnie wysłucham jeszcze jakiś propozycji. Szczerze powiedziawszy gdy pokazałeś EXISTS to już miałem cień nadziei że zadziała.

[BaN]

ad1) Bawiłem się już i taką kombinacją - nic to nie dało

Dziwne, baza powinna skorzystać z podwójnego indeksu. Jakaś stara wersja bazy? To w takim razie wymuś skorzystanie z indeksu za pomocą FORCE INDEX. Jaki jest EXPLAIN?

Kombinuj dalej. Chętnie wysłucham jeszcze jakiś propozycji. Szczerze powiedziawszy gdy pokazałeś EXISTS to już miałem cień nadziei że zadziała.

Nie mam danych i tabel, a nie chce mi się wpisywać Kolejna propozycja: użyj UNION

[nospor]

FORCE INDEX rzeczywiście przyspieszyło zapytanie. Aczkolwiek dla pewnych warunków danych. Trzeba by się pobawić i dobrać optymalne. Nie mniej jednak FORCE INDEX nie rozwiązał całkowicie problemu, gdyż nadal za wolno dane się szukały.

Na UNION wpadłem przed Twoim postem. Potestowałem i dalo zadowalające wyniki. Chyba więc zostanę przy UNION, szczególnie gdyż będę miał jeszcze kilka złączeń i warunków oprócz tych co tu podałem.