mysql full text search

MySQL ile bir arama işlemi gerçekleştirmek istediğimizde Where anahtar kelimesini kullanırız. Aşağıda products tablosunda id alanı, 3'e eşit olan kaydı getiren bir sorgu var. Genelde böyle bir arama işlemi gerçekleştirdiğimizde sonuca hızlı bir şekilde ulaşırız.

select * from product_translations where id = 3

Peki sözel bir ifadenin içinde arama yapmak istersek: O zaman LIKE anahtar sözcüğünü kullanmamız gerekir. Aşağıdaki sorgu products tablosunun name kolonu içerisinde "matlab" kelimesini içeren kayıtları getirir.

select * from product_translations where name like '%matlab%'

Peki bu şekilde arama yapmak performanslı mıdır? Az sayıda veride bu şekilde arama yapmanın gözle görülür bir performans düşüklüğü yoktur fakat çok fazla veride bu şekilde arama işlemi gerçekleştirmek uzun saniyeler sürebilir. Canlı bir örnekle bu durumu yakından inceleyelim.

NOT: Örnek için kullanılan tablo içerisinde toplamda 410.000 kayıt bulunmaktadır.

Aşağıdaki görselde product_description_translations tablosunda content alanında "bildirim" dizesini içeren kayıtları getirmesini istedik.

Görüldüğü üzere bu işlemin tamamlanması 12.2 saniye sürdü. Bu çok uzun bir süre ve veritabanı trafiğine göre dahada artış gösterebilir. Şimdi full text search ile aynı işlemi gerçekleştirelim.

Aşağıda görüldüğü üzere AGAINST anahtar kelimesi ile full text search işlemi gerçekleştirdik ve aynı işlemi 0.6 saniyede tamamladık.

NOT: Full text search işlemini gerçekleştirmek için tablomuz oluşturulurken full text aramasına dahil olacak kolonları belirtmemiz gerekir veya tablomuz oluştuktan sonra kolonları full text aramaları için kullanılabilir hale getirebiliriz.

CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
name VARCHAR(200), 
description TEXT, 
FULLTEXT(name,description)
) ENGINE=InnoDB;

//OR
ALTER TABLE  table_name  
ADD FULLTEXT(column_name1, column_name2,…)

NOT: Arama yaparken kullanabileceğimiz tüm modların listesi aşağıda bulunmaktadır. Bu modların detaylı bilgisine https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html adresinden ulaşabilirsiniz.

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

Şimdi gelelim bu işlemin normal search ile arasındaki farklara ve neden sonuca bu kadar hızlı ulaşabildiğimize.

LIKE ile search ettiğimizde sırayla tüm satırlarda bulunan content kolonu içerisinde 'bildirim' dizesinin geçip geçmediği kontrol edilir yani burada yapılan arama işlemi yatay arama işlemi olarak adlandırılır. Content içeriğini ayrıştırmak daha sonra aramak ve bunun her satır için tekrar etmek işlemin uzun sürmesine neden olmaktadır. Tüm arama algoritmalarında dikey arama yatay aramaya göre çok daha hızlıdır. Bu yüzden where id = 3 gibi bir arama yaptığımızda sonuca çok hızlı ulaşırız. Çünkü arama işlemi yukarıdan aşağıya dikey olarak gerçekleşir.

Full Text Search işleminde de content içerisinde bulunan tüm veriler arka planda kelimelerine ayrılıp satır satır tutulduğu için aramayı yatayda yapmak yerine dikeyde gerçekleştiriyoruz. Böylece yatay aramanın hantallığından kurtularak dikey arama ile çok daha hızlı şekilde sonucumuzu elde ediyoruz.