SELECT WHERE NOT IN - podzapytanie, Strasznie wolno... Opcje

[geogis]

Witam,

Mam problem z pewnym zadaniem. Mam jedno rozwiązanie ale jakoś mnie to nie zadowala.

Chodzi o takie zapytanie:

[SQL] pobierz, plaintext 
SELECT * FROM users u  WHERE u.image<>'' AND u.user_id<>'15' AND u.user_id NOT IN (SELECT v.user_id FROM voting v WHERE v.voter_id='15') ORDER BY RAND()  LIMIT 1
[SQL] pobierz, plaintext 

w tabeli 'users' jest kilka tysięcy wpisów a w voting jest tez kilka tysiecy (ale bedzie o wiele wiecej - bo tu zapisywane sa dane zwiazane z pewnym glosowaniem na stronie i kazdy użytkownik glosuje po setki razy). Takie zapytanie niestety jest tak pamięciożerne że siada mi cała strona.

Zmieniłem to na takie coś że najpierw zbieram dane do tabeli z tego zapytania:

[SQL] pobierz, plaintext 
SELECT v.user_id FROM voting v WHERE v.voter_id='15'
[SQL] pobierz, plaintext 

i podstawiam do zapytania gotową listę użytkowników:

[SQL] pobierz, plaintext 
SELECT * FROM users u  WHERE u.image<>'' AND u.user_id<>'15' AND u.user_id NOT IN ($tablica_uzytkownikow) ORDER BY RAND()  LIMIT 1
[SQL] pobierz, plaintext 

ale wyczytałem na róznych forach dyskusyjnych że NOT IN generalnie nie jest szybkie i ze jak $tabela_uzytkownikow bedzie zawierac tysiace wartosci to moze powodować to duże opóźnienia...

Jakiś pomysł na inne rozwiazanie?

Generalnie wyglada to tak że uzytkownik A dostaje losowego użytkownika do glosowania (powidzmy ze glosuje na to czy mu sie podoba czy nie). Ma dwie odpowiedzi. TAK lub NIE. Po zaglosowaniu zapisywane jest do bazy do tabeli VOTING voter_id (czyli ten uzytkownik A), user_id (czyli ta osoba na która glosowano) no i sama odpowiedz.

Po zaglosowaniu trzeba wylosowac nastepnego uzytkownika do głosowania, ale oczywiscie nie moze to być osoba na którą juz glosowalismy

Tabele mamy takie:

USERS:
user_id
image

VOTING
voter_id
user_id


Z gory dzieki za pomysły!

[Crozin]

1. Załącz wyniki EXPLAIN SELECT ....
2. Stawiam raczej, że to ORDER BY RAND() odpowiedzialne jest za powolny czas wykonywania się zapytania.

[ylk]

Jeśli kwestia dotyczy tylko powolności NOT IN, mozna w PHP zrobic petle, ktora sformuluje zapytanie z pominieciem NOT IN:

[PHP] pobierz, plaintext 
$q="SELECT * FROM users u  WHERE u.image<>'' AND u.user_id<>'15' AND ";
foreach ($tablica_uzytkownikow as $user) $q.="u.user_id!='$user' AND ";
$q=substr($q,0,-4); // obciecie ostatniego ANDa
$q.=" ORDER BY RAND()  LIMIT 1";
[PHP] pobierz, plaintext 

[wookieb]

Jeśli kwestia dotyczy tylko powolności NOT IN, mozna w PHP zrobic petle, ktora sformuluje zapytanie z pominieciem NOT IN:

[PHP] pobierz, plaintext 
$q="SELECT * FROM users u  WHERE u.image<>'' AND u.user_id<>'15' AND ";
foreach ($tablica_uzytkownikow as $user) $q.="u.user_id!='$user' AND ";
$q=substr($q,0,-4); // obciecie ostatniego ANDa
$q.=" ORDER BY RAND()  LIMIT 1";
[PHP] pobierz, plaintext 

Nie ma różnicy dla bazy danych. To identyczna operacja logiczna.

[Pilsener]

Moim zdaniem nie da się tego tak wprost zoptymalizować, lecz nie jesteśmy bezsilni:
- losujemy ID jakiegoś usera i sprawdzamy, czy możemy na niego głosować - jeśli nie losujemy następne ID (to będzie dobre przy dużej liczbie userów gdy wiemy, że user A nie jest w stanie zagłosować na więcej niż kilka procent pozostałych userów)
- zawężamy zakres order by rand() losując dwuetapowo, najpierw losujemy jakiś przedział rekordów (np. 6700<ID<6800) i dopiero na nich wykonujemy order by rand

Pisałem o tym także tutaj:
http://forum.php.pl/index.php?s=&showt...st&p=817847

[Noidea]

@geogis Generalnie zapytania z IN( podzapytanie ) lub NOT IN( podzapytanie ) można optymalizować stosując złączenia tabel. W twoim przypadku będzie to zapytanie, które:
Pobiera wszystko
z tabeli users
do której dołączona jest tabela voting w taki sposób, że jeśli user15 głosował na tego użytkownika, to dołączone są dane głosowania, a w przeciwnym wypadku NULLe
i wybieramy wiersze, które mają NULLe w danych z tabeli głosowania

[SQL] pobierz, plaintext 
SELECT u.* 
FROM users u
LEFT JOIN voting v ON ( v.voter_id = 15 AND u.user_id = v.user_id )
WHERE v.user_id IS NULL AND u.user_id <> 15
-- ORDER BY RAND() LIMIT 1
[SQL] pobierz, plaintext 

Jeśli masz dobrze pozakładane indeksy, to EXPLAIN powinien wyświetlić miłe dla oka informacje. ALE tak samo jak inni uważam, że na wydajności tracisz głównie przez ORDER BY RAND(). Są sposoby na jego optymalizację, poszukaj w necie.

[geogis]

@Noidea

W Twoim przykładzie chyba nie uzyskyje tego co mi potrzeba. Przy założeniu że na tą samą osobe mogę glosowanc inni uzytkownicy wartość nie bedzie NULL wiec trzeba wykluczyć numery user_id na które już głosowałem.

@Pilsener

Pomysły godne wypróbowania.

@Crozin

Dobrze by było skorzystać z EXPLAIN ale juz nie chce nawet wywoływać tego zapytania które mi zawiesza strone... Nie chce zrażać do własnej strony swoich użytkowników. Ale coś pokombinuje z tymi obecnymi zapytaniami. Nigdy nie korzystałem z EXPLAIN wiec dzieki za podpowiedz!

Dzieki wielkie wszystkim!

[Noidea]

W Twoim przykładzie chyba nie uzyskyje tego co mi potrzeba. Przy założeniu że na tą samą osobe mogę glosowanc inni uzytkownicy wartość nie bedzie NULL wiec trzeba wykluczyć numery user_id na które już głosowałem.

I właśnie dlatego w złączeniu oprócz u.user_id = v.user_id jest jeszcze v.voter_id = 15

LEFT JOIN voting v ON ( v.voter_id = 15 AND u.user_id = v.user_id )

Jeśli będziesz chciał wykonać to zapytanie dla innego użytkownika, to "15" musisz zmienić w obu miejscach ( LEFT JOIN oraz WHERE)


PS. EXPLAIN nie wykonuje zapytania, tylko go opisuje. Zawsze działa szybko. Nie mniej jednak nie testuj zapytań w środowisku produkcyjnym (na stronie). Zrób sobie zrzut bazy, wgraj go u siebie na kompie i tam testuj.

[geogis]

Hej, dzieki wielkie za pomoc. Oczywiscie w srodowisku produkcyjnym nie powinno sie tego robic... ale człowiekowi sie nie chce robić kopii jak czasu na nic nie ma bo robity za dużo do zrobienia (IMG:style_emoticons/default/wink.gif)