O ile pobranie przeczytanych rzeczy jest łatwe(uzytkownik.wiadomosci) to pojawia się problem z pobraniem tylko nieprzeczytanych.
Myślałem nad takim rozwiązaniem:
SELECT wiadomosci.* FROM wiadomosci WHERE wiadomosci.id NOT IN (SELECT wiadomosc_id FROM przeczytane WHERE (przeczytane.uzytkownik_id = ? ))
jednak boję się że przy większej ilości przeczytanych wiadomości może mi serwer nie wyrobić. Zna ktoś jakiś inny lżejszy sposób na rozwiązanie tego problemu?
Czy jedna wiadomość może należeć tylko do jednego użytkownika? Jeśli tak to sprawę załatwi atrybut boolean “read” w modelu Wiadomosc (polecam jednak używać angielskich nazw).
“Premature optimization is root of all evil”. Z dobrymi indeksami rozwiązanie, który wybrałeś starczy na bardzo długo.
[code]underley=# SELECT count(*) from messages where rcptto_id = 5 and id not in (SELECT message_id from messages_read where user_id = 5);
count
52488
(1 row)
underley=# SELECT count(*) from messages_read where user_id = 5;
count
1674
(1 row)
underley=# EXPLAIN ANALYZE SELECT * from messages where rcptto_id = 5 and id not in (SELECT message_id from messages_read where user_id = 5);
QUERY PLAN
Bitmap Heap Scan on messages (cost=1101.74…5045.39 rows=27088 width=22) (actual time=13.160…63.633 rows=52488 loops=1)
Recheck Cond: (rcptto_id = 5)
Filter: (NOT (hashed subplan))
-> Bitmap Index Scan on idx_messages_rcptto_id (cost=0.00…962.63 rows=54177 width=0) (actual time=9.565…9.565 rows=54162 loops=1)
Index Cond: (rcptto_id = 5)
SubPlan
-> Bitmap Heap Scan on messages_read (cost=33.23…128.15 rows=1674 width=4) (actual time=0.311…1.498 rows=1674 loops=1)
Recheck Cond: (user_id = 5)
-> Bitmap Index Scan on messages_read_uniq_uid_msgid (cost=0.00…32.81 rows=1674 width=0) (actual time=0.292…0.292 rows=1674 loops=1)
Index Cond: (user_id = 5)
Total runtime: 80.049 ms
(11 rows)[/code]
Sprzęt nie jest jakiś wypasiony, zwykły developerski desktop.
[quote=macbury]Mam stronę z wiadomościami i tak połączone modele
Wiadomosc < Przeczytane > Uzytkownik
O ile pobranie przeczytanych rzeczy jest łatwe(uzytkownik.wiadomosci) to pojawia się problem z pobraniem tylko nieprzeczytanych.
Myślałem nad takim rozwiązaniem:
SELECT wiadomosci.* FROM wiadomosci WHERE wiadomosci.id NOT IN (SELECT wiadomosc_id FROM przeczytane WHERE (przeczytane.uzytkownik_id = ? ))
jednak boję się że przy większej ilości przeczytanych wiadomości może mi serwer nie wyrobić. Zna ktoś jakiś inny lżejszy sposób na rozwiązanie tego problemu?[/quote]
Wydaje mi się, że najłatwiej będzie tak:
all_messages = Message.find(:all)
user_messages = current_user.messages
diff = all_messages - user_messages
Nie jestem pewien czy takie DEPENDENT SUBQUERY jest optymalizowane w jakis sposob czy nie, mam wrazenie ze taka operacja bedzie wykonana dla kazdego wiersza w tabeli, ale byc moze tak proste zapytanie jest optymalizowane. W kazdym badz razie zapisanie tego w formie join’a powinno tez zadzialac:
SELECT * FROM wiadomosci w LEFT OUTER JOIN przeczytane p ON w.id = p.id AND p.uzytkownik_id = 2828
I teraz wszystie te ktore beda przeczytane beda posiadaly wartosc inna od NULL tak mi sie wydaje przynajmnije w teorii natomiast te ktore beda nie przeczytane dla wszystkich kolumn p.* beda posiadaly wartosci NULL miedzy innymi p.id a wiec latwo sobie tutaj juz dobrac warunki.
np
SELECT * FROM wiadomosci w LEFT OUTER JOIN przeczytane p ON w.id = p.id AND p.uzytkownik_id = 2828 WHERE p.id IS NULL
pokaze wszystkie wiadomosci nie przeczytane
Czyli dla AC powinno to wygladac mniejwiecej tak:
Wiadomosci.find(:joins => “LEFT OUTER JOIN przeczytane p ON wiadomosci.id = p.id AND p.uzytkownik_id = #{current_user.id}”, :conditions => “p.id IS NULL”)
Nie mam na czym potestowac wiec jest to czysta teoria.
Zadziała, ale będzie wolniejsze od ‘not in’ - będzie zawsze robić seq scan na tabeli z wiadomościami i chyba nie da się tego seq scana wyeliminować.
W praktyce, przy jakichś realnych ilościach wiadomości nie będzie to miało żadnego znacznia.
Test dla 10k rekordów w messages.
User, który ma dużo więcej wiadomości przeczytanych niż nieprzeczytanych:
[code]underley=# EXPLAIN ANALYZE SELECT * FROM messages as m LEFT OUTER JOIN messages_read as mr ON (m.id = mr.message_id AND mr.user_id = 5) WHERE mr.id IS NULL;
QUERY PLAN
Hash Left Join (cost=430.55…972.35 rows=5000 width=30) (actual time=12.511…27.977 rows=1958 loops=1)
Hash Cond: (m.id = mr.message_id)
Filter: (mr.id IS NULL)
-> Seq Scan on messages m (cost=0.00…159.00 rows=10000 width=18) (actual time=0.010…6.575 rows=10000 loops=1)
-> Hash (cost=342.68…342.68 rows=7030 width=12) (actual time=12.476…12.476 rows=8042 loops=1)
-> Seq Scan on messages_read mr (cost=0.00…342.68 rows=7030 width=12) (actual time=0.008…7.468 rows=8042 loops=1)
Filter: (user_id = 5)
Total runtime: 28.677 ms
(8 rows)
underley=# EXPLAIN ANALYZE SELECT * from messages where id not in (SELECT message_id from messages_read where user_id = 5);
QUERY PLAN
Seq Scan on messages (cost=360.25…544.25 rows=5000 width=18) (actual time=13.237…20.227 rows=1958 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on messages_read (cost=0.00…342.68 rows=7030 width=4) (actual time=0.010…7.863 rows=8042 loops=1)
Filter: (user_id = 5)
Total runtime: 20.884 ms
(6 rows)[/code]
odwrotne proporcje:
[code]underley=# EXPLAIN ANALYZE SELECT * FROM messages as m LEFT OUTER JOIN messages_read as mr ON (m.id = mr.message_id AND mr.user_id = 6) WHERE mr.id IS NULL;
QUERY PLAN
Hash Left Join (cost=222.96…531.91 rows=5000 width=30) (actual time=3.001…18.547 rows=7711 loops=1)
Hash Cond: (m.id = mr.message_id)
Filter: (mr.id IS NULL)
-> Seq Scan on messages m (cost=0.00…159.00 rows=10000 width=18) (actual time=0.009…6.340 rows=10000 loops=1)
-> Hash (cost=191.78…191.78 rows=2495 width=12) (actual time=2.983…2.983 rows=2289 loops=1)
-> Bitmap Heap Scan on messages_read mr (cost=63.59…191.78 rows=2495 width=12) (actual time=0.432…1.541 rows=2289 loops=1)
Recheck Cond: (user_id = 6)
-> Bitmap Index Scan on messages_read_uniq_uid_msgid (cost=0.00…62.97 rows=2495 width=0) (actual time=0.418…0.418 rows=2289 loops=1)
Index Cond: (user_id = 6)
Total runtime: 21.050 ms
(10 rows)
underley=# EXPLAIN ANALYZE SELECT * from messages where id not in (SELECT message_id from messages_read where user_id = 6);
QUERY PLAN
Seq Scan on messages (cost=198.01…382.01 rows=5000 width=18) (actual time=3.307…11.851 rows=7711 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Bitmap Heap Scan on messages_read (cost=63.59…191.78 rows=2495 width=4) (actual time=0.416…1.731 rows=2289 loops=1)
Recheck Cond: (user_id = 6)
-> Bitmap Index Scan on messages_read_uniq_uid_msgid (cost=0.00…62.97 rows=2495 width=0) (actual time=0.400…0.400 rows=2289 loops=1)
Index Cond: (user_id = 6)
Total runtime: 14.293 ms
(8 rows)[/code]
select * from messages m right join (select * from readed_messages where user_id = 2828) rm on m.id = rm.message_id;
Tak powinienes rozjechac ta baze
Umnie na mysql z 5 sekund dla SUBQUERY spadlo do 0.02 sekundy
mysql> EXPLAIN select * from messages m right join (select * from readed_messages where user_id = 2828) rm on m.id = rm.message_id;
+----+-------------+-----------------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+---------------+---------+---------+---------------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3176 | |
| 1 | PRIMARY | m | eq_ref | PRIMARY | PRIMARY | 4 | rm.message_id | 1 | |
| 2 | DERIVED | readed_messages | ALL | NULL | NULL | NULL | NULL | 2952 | Using where |
+----+-------------+-----------------+--------+---------------+---------+---------+---------------+------+-------------+
Przy okazji wymysli ktos latwiejszy sposob na wylosowanie n unikalnych liczb losowych ?
moj jest taki dla n = 5000
(1..(20*n)).map { rand(n) }.uniq[0...n]
Chyba krocej sie juz nie da ? dobre pytanie jest dlaczego akurat przy 20n mamy pewnosc ze bedzie tych liczb dokladnie n, przy 10n mozna bylo ich miec czasami n-1, ma ktos prosta odpowiedz na to jaka jest tu zaleznosc ? Nie jest to napewno totalnie losowe poniewaz zmienia sie to dosyc linearnie.
A, troche sie zapedzilem pewnie dlatego ze jest juz pozno powyzsze zapytanie zwraca wszystkie przeczytane, natomiast nieprzeczytane otrzymamy uzywajac left join + where
mysql> EXPLAIN select * from messages m left join (select * from readed_messages where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NULL;
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------------------+
| 1 | PRIMARY | m | ALL | NULL | NULL | NULL | NULL | 5178 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3176 | Using where; Not exists |
| 2 | DERIVED | readed_messages | ALL | NULL | NULL | NULL | NULL | 2952 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------------------+
z 5 sekund do 1 to calkiem sporo, baza nie ma zadnych indeksow, konfig defaultowy, baza MyISAM a wiec spekuluje ze z innodb przy dobrze skonfigurowanej bazie mozna by zejsc do 200-300 ms
Zastanawiam sie tylko czy MySQL nie ma jakiegos buga lub miejsca w ktorym mozna by zoptymalizowac cos, roznica pomeidzy rm.id IS NULL a rm.id IS NOT NULL to az 1200 %
99.9 % czasu zapytania select count(*) from messages m left join (select * from readed_messages where user_id = 2828) rm on m.id = rm.message_id; to wartosci wpisanie wszedzie NULL.
Bez warunku: 1.30 s
warunek IS NULL: 1.20
warunek IS NOT NULL: 0.01
Najlepszą optymalizacją będzie użycie jakiejś prawdziwej bazy danych
A co do tematu - to tak jak napisałem na samym poczatku - nie ma co optymalizować w tej chwili, szkoda czasu.
Dlaczego? Autor pewnie jeszcze nie wie, jak będzie wygladała ilość rekordów w poszczególnych tabelach, więc teraz to tylko gdybanie i teoretyzowanie.
Owszem, można zrobic pewne założenia - ale wtedy i tak wychodzi, że zapytanie z subselectem i not in jest wystarczająco dobre.
Ja osobiście stawiam na to, że wiadomości mozę być kilkadziesiąt, max kilkaset, a informacji o przeczytaniu wiadomości będzie ilość wiadomości * ilość userów. Wtedy jedyną konieczną optymalizacją będzie założenie indeksu na tabeli z informacjami o przeczytanych, powinna wystarczyć na kolumnie z id usera.
W wydaniu uzytkownikow pgsql te slowa zrobily sie juz nudne odcinam sie wiec od kontynuowania tego offtopa
Natomiast jestem bardzo ciekaw czy na pgsql jest ten sam wzrost wydajnosci dla takiego zapytania (powinno byc kompatybilne z tym co masz w bazie):
select * from messages m left join (select * from messages_read where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NULL;
I jakie masz wyniki dla
select count(*) from messages m left join (select * from messages_read where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NULL;
oraz
select count(*) from messages m left join (select * from messages_read where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NOT NULL;
P.S. jesli juz pytanie zostalo postawione to odpowiadanie w stylu “to pytanie jest zadane zbyt wczesnie” jest bardziej evil niz jakakolwiek, nawet glupia odpowiedz Ale fakt faktem, sql najlepiej optymalizowac na przestrzeni kilku-kilkunastu dni pracujacej juz aplikacji, gdy mamy do dyspozycji slow query log’a oraz jestesmy w stanie mierzyc cpu load, ale tez nie zawsze, wiele rzeczy jesli sie posiada odpowiednia wiedze mozna zrobic juz na samym poczatku, czasem nawet trzeba bo przeciez znacznie wiecej moze cie kosztowac przebudowanie bazy (i w sql i w activerecord) jesli zaprojektowales ja w zly sposob.
[quote=PaK]Natomiast jestem bardzo ciekaw czy na pgsql jest ten sam wzrost wydajnosci dla takiego zapytania (powinno byc kompatybilne z tym co masz w bazie):
select * from messages m left join (select * from messages_read where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NULL;[/quote]
[code]underley=# EXPLAIN ANALYZE select * from messages m left join (select * from messages_read where user_id = 5) rm on m.id = rm.message_id where rm.id IS NULL;
QUERY PLAN
Hash Left Join (cost=436.63…988.55 rows=5000 width=30) (actual time=10.373…25.412 rows=1958 loops=1)
Hash Cond: (m.id = messages_read.message_id)
Filter: (messages_read.id IS NULL)
-> Seq Scan on messages m (cost=0.00…159.00 rows=10000 width=18) (actual time=0.010…6.276 rows=10000 loops=1)
-> Hash (cost=336.10…336.10 rows=8042 width=12) (actual time=10.338…10.338 rows=8042 loops=1)
-> Bitmap Heap Scan on messages_read (cost=138.58…336.10 rows=8042 width=12) (actual time=1.322…5.170 rows=8042 loops=1)
Recheck Cond: (user_id = 5)
-> Bitmap Index Scan on idx_mr_uid (cost=0.00…136.57 rows=8042 width=0) (actual time=1.300…1.300 rows=8042 loops=1)
Index Cond: (user_id = 5)
Total runtime: 26.075 ms
(10 rows)[/code]
[quote]I jakie masz wyniki dla
select count(*) from messages m left join (select * from messages_read where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NULL;[/quote]
[code]underley=# EXPLAIN ANALYZE select count(*) from messages m left join (select * from messages_read where user_id = 5) rm on m.id = rm.message_id where rm.id IS NULL;
QUERY PLAN
Aggregate (cost=1001.05…1001.06 rows=1 width=0) (actual time=25.638…25.638 rows=1 loops=1)
-> Hash Left Join (cost=436.63…988.55 rows=5000 width=0) (actual time=11.082…24.897 rows=1958 loops=1)
Hash Cond: (m.id = messages_read.message_id)
Filter: (messages_read.id IS NULL)
-> Seq Scan on messages m (cost=0.00…159.00 rows=10000 width=4) (actual time=0.009…5.401 rows=10000 loops=1)
-> Hash (cost=336.10…336.10 rows=8042 width=8) (actual time=11.049…11.049 rows=8042 loops=1)
-> Bitmap Heap Scan on messages_read (cost=138.58…336.10 rows=8042 width=8) (actual time=1.362…6.079 rows=8042 loops=1)
Recheck Cond: (user_id = 5)
-> Bitmap Index Scan on idx_mr_uid (cost=0.00…136.57 rows=8042 width=0) (actual time=1.339…1.339 rows=8042 loops=1)
Index Cond: (user_id = 5)
Total runtime: 25.693 ms
(11 rows)[/code]
[quote]oraz
select count(*) from messages m left join (select * from messages_read where user_id = 2828) rm on m.id = rm.message_id where rm.id IS NOT NULL;[/quote]
[code]underley=# EXPLAIN ANALYZE select count(*) from messages m left join (select * from messages_read where user_id = 5) rm on m.id = rm.message_id where rm.id IS NOT NULL;
QUERY PLAN
Aggregate (cost=847.47…847.48 rows=1 width=0) (actual time=32.365…32.366 rows=1 loops=1)
-> Hash Join (cost=284.00…827.36 rows=8042 width=0) (actual time=11.357…29.422 rows=8042 loops=1)
Hash Cond: (messages_read.message_id = m.id)
-> Seq Scan on messages_read (cost=0.00…342.31 rows=8042 width=4) (actual time=0.012…8.545 rows=8042 loops=1)
Filter: ((id IS NOT NULL) AND (user_id = 5))
-> Hash (cost=159.00…159.00 rows=10000 width=4) (actual time=11.335…11.335 rows=10000 loops=1)
-> Seq Scan on messages m (cost=0.00…159.00 rows=10000 width=4) (actual time=0.007…5.503 rows=10000 loops=1)
Total runtime: 32.443 ms
(8 rows)[/code]
Jak widać różnica kosztu ~16%, a czasu ~22% - do 1200% duuuużo brakuje.
Aha - różnica może wynikać z rozkładu danych - ile masz rekordów w messages, a ile w read dla user którego testujesz?
Ale pytanie było inne i odpowiedź była też inna
Owszem - nic nie zastępi tutaj doświadczenia i znajomości teorii.
natomiast te ktore beda nie przeczytane dla wszystkich kolumn p.* beda posiadaly wartosci NULL miedzy innymi p.id a wiec latwo sobie tutaj juz dobrac warunki.
powyzsze zapytanie zwraca wszystkie przeczytane, natomiast nieprzeczytane otrzymamy uzywajac left join + where
Ale fakt faktem, sql najlepiej optymalizowac na przestrzeni kilku-kilkunastu dni pracujacej juz aplikacji, gdy mamy do dyspozycji slow query log’a oraz jestesmy w stanie mierzyc cpu load, ale tez nie zawsze, wiele rzeczy jesli sie posiada odpowiednia wiedze mozna zrobic juz na samym poczatku, czasem nawet trzeba bo przeciez znacznie wiecej moze cie kosztowac przebudowanie bazy (i w sql i w activerecord) jesli zaprojektowales ja w zly sposob.