Re: Veri özeti, Like ile arama performans'ı

Merhaba...

Öncelikle SQL üzerinde çok fazla bir bilgi ve tecrübeye sahip olmadığımı
belirteyim.

Şu sıralar PHP/PostgreSQL kullanarak yazdığım internet ilan sitesi var.
ve sistemi en çok zorlayacak olan durumun arama olduğuna karar verdim çünkü
toplamda 9 ayrı kategoriye göre arama yapabilmeli ve bu 9 ayrı kategorinin
değişik kombinasyonları olabilmeli idi. Bir arkadaşım ile bu tür bir aramayı
en optimize şekilde nasıl yapalır diye konuştumuğumuzda şöyle bir çözüm
önerisi geldi.

Her kategorinin kendine ait uniq değerleri var bunları birbirinin peşi sıra
ekle, tek bir veri elde et ve elde ettiğin verinin içinde like ile arama yap.

Biraz daha açmak gerekir ise:

101001704604450206 diye bir veri özeti var bu veri özeti bana şunu söylüyor.
1 => resmi var,
01=> şehir id'si
0017=> ilçe id'si
046=> marka id'si
0445 => model id'si
02 => Tür id'si
06 => renk id'si

Böylece tek bir sorgu ile toplam 7 ayrı kategoride like ile arama
yapabiliyorum.

tablo yapım ise

create table search_test (
ilan_id bigint not null,
search_sum varchar(50) not null
);

create index search_test_index on search_test(search_sum);

Yukarıdaki veri özeti gibi toplam 2 milyon kaydım var (sadece test için
rastgele 2 milyon kayıt girdim ) ve bu iki milyon kayıt için aşağıdaki
sorguyu çalıştırdığımda ortalama sonuç süresi 2.10 sn. sürüyor.

select * from search_test where search_sum like '1__0017_______02__';

Elbette tam çalışma tamamlandığında yukarıdaki arama cümlesi 2 milyon kayıtta
arama yapmayacak. (Dışarıda kalan 2 kategoriye göre süzme işlemi yapıp daha
sonra bu sorgu çalıştırılacak. ancak diğer iki kategori yine 2 şer milyon
kayıt üzerinde çalışacak.)

Eğer tek bir kategori ile arama yapılırsa bu sefer 10 sn. üzerinde sonuç
dönüyor.

Özet olarak sadece düz iki milyon kayıt için yukardaki gibi bir yapıda daha
hızlı arama yapabilmek için nasıl bir yol izlemeliyim. Like yerine ~ ile
arama yaptığımda çok daha fazla zaman aldığını belirteyim.

--
Saygılar && İyi çalışmalar
Timu EREN (a.k.a selam)

Merhaba,

On 11/27/05, Timu EREN <selamtux(at)gmail(dot)com> wrote:
> select * from search_test where search_sum like '1__0017_______02__';

Eğer bir veritabanını bu şekilde kullanacaksanız hiç kullanmayın daha
iyi. Bu kesinlikle veritabanı mantığına aykırı bir hareket.

İlk önerim şu olacak: (Kolay ama ikinci önereceğim yönteme oranla daha
düşük performanslı.) Belli katagoriler için farklı tablolar yaratın.
Yani katagorizasyon yelpazesini biraz daha daraltın. Ve ek olarak, her
alan üstünde INDEX yaratın. Hatta hatta, çok sık tarama yaptığınız
alanları, birlikte INDEX'leyin. (Bu konu hakkında Devrim Bey daha
ayrıntılı bilgi verebilir.) Bir de contrib altında gelen tsearch2'ye
bakın. O sizin ihtiyacına göre biraz kalın gelecek ama elinizi
kirleterek istediğiniz büyüklüğü ölçekleyebilirsiniz.

İkinci önerim şu olacak: Gördüğüm kadarı ile yaptığınız işin
(R)DBMS'lik bir tarafı yok. Yani veritabanının kullanılma nedenlerinin
biraz dışına taşıyor sizinkisi. Hele ki belirttiğiniz büyüklükte bir
veri için istediğiniz performansı düşünürsek. Verinizi yukarıdaki
biçimde metin dosyalarında saklayın ve arama yapmak için piyasadaki
bir çok arama algoritmasından birini elinizden geldiği kadar low-level
olarak uyarlayın. Örneğin:

- http://en.wikipedia.org/wiki/Knuth-Morris-Pratt_algorithm
- http://en.wikipedia.org/wiki/Rabin-Karp_string_search_algorithm

(Olayın hayvanlık tarafı için, okuduğum bir kitabı önereyim: Handbook
of Exact String-Matching Algorithms. Garanti ederim konu hakkında
doyumsuz kalmayacaksınız.)

Son yöntem ile emin olun epey bir performans artışı yaşayacaksınız.
Hele bir de bu metin dosyasını tmpfs ile bellek üzerinde tutarsanız...

Peki ben bu yöntemde ya yeni bir kayıt eklemek istersem, ya da belirli
bir biçime uyanları silmek istersem, onun için de ayrı bir program mı
yazacağım diyebilirsiniz. Bu durumda, (ilgili verinin zırt pırt
değişmediğini varsayarak) asıl tüm katagorileri 1. önerimdeki gibi
veritabanında tutun, yaptığınız her değişiklikten sonra yazdığınız
RULE'lar ile aramanın asıl yapılacağı metin dosyası oluşturulsun.

Unutmadan, rakamlarınız bana çok astronomik geldi. Bence
oluşturduğunuz şablonda bir mantık hatası olması ihtimali yüksek.
Çünkü zannetmiyorum ki Google'ın AdSense'inde bile bu kadar satır
olsun.

İyi çalışmalar.

Unutmadan, bahsi geçen ikinci yöntemi kullanırken, programı C (ya da
muadili bir dil) ile geliştirirseniz, derlenmiş fonksiyonları
PostgreSQL'den çağırabilirsiniz. Ayrıntılı bilgi için PostgreSQL
dökümantasyonundaki ilgili "Extending SQL" alt bölümüne (32.9)
bakabilirsiniz.

On 11/28/05, Volkan YAZICI <volkan(dot)yazici(at)gmail(dot)com> wrote:
> İkinci önerim şu olacak: Gördüğüm kadarı ile yaptığınız işin
> (R)DBMS'lik bir tarafı yok. Yani veritabanının kullanılma nedenlerinin
> biraz dışına taşıyor sizinkisi. Hele ki belirttiğiniz büyüklükte bir
> veri için istediğiniz performansı düşünürsek. Verinizi yukarıdaki
> biçimde metin dosyalarında saklayın ve arama yapmak için piyasadaki
> bir çok arama algoritmasından birini elinizden geldiği kadar low-level
> olarak uyarlayın. Örneğin:
>
> - http://en.wikipedia.org/wiki/Knuth-Morris-Pratt_algorithm
> - http://en.wikipedia.org/wiki/Rabin-Karp_string_search_algorithm
>
> (Olayın hayvanlık tarafı için, okuduğum bir kitabı önereyim: Handbook
> of Exact String-Matching Algorithms. Garanti ederim konu hakkında
> doyumsuz kalmayacaksınız.)
>
> Son yöntem ile emin olun epey bir performans artışı yaşayacaksınız.
> Hele bir de bu metin dosyasını tmpfs ile bellek üzerinde tutarsanız...
>
> Peki ben bu yöntemde ya yeni bir kayıt eklemek istersem, ya da belirli
> bir biçime uyanları silmek istersem, onun için de ayrı bir program mı
> yazacağım diyebilirsiniz. Bu durumda, (ilgili verinin zırt pırt
> değişmediğini varsayarak) asıl tüm katagorileri 1. önerimdeki gibi
> veritabanında tutun, yaptığınız her değişiklikten sonra yazdığınız
> RULE'lar ile aramanın asıl yapılacağı metin dosyası oluşturulsun.

Sorunun b.ku çıkacak ama böyle güzel bir projeyi de harcamak olmaz:

WHAT is OpenFTS?
OpenFTS (Open Source Full Text Search engine) is an advanced
PostgreSQL-based search engine that provides online indexing
of data and relevance ranking for database searching. Close
integration with database allows use of metadata to restrict
search results.
[http://openfts.sourceforge.net/]

Hani olur ya, listede tek deli ben değilimdir.