WordPressin haku nopeaksi MariaDB:llä

WordPressin sisäänrakennettua hakutoimintoa ei voi aina kehua nopeaksi. Se hidastelee helposti varsinkin suurilla sivustoilla, kun tietokannassa on paljon sisältöä. Tätä ongelmaa on yritetty vuosien mittaan ratkaista monin eri tavoin. Me olemme Seravolla kehittäneet oman ratkaisumme WordPressin vakiohaun täydentämiseen, jonka uskomme olevan nopeampi kuin useimmat aiemmista ratkaisuista.

Miksi WordPressin haku on hidas?

WordPress tekee vakiona varsin laajoja SQL-kyselyjä haun toteuttamiseksi. Jo useiden JOIN-lausekkeiden käyttö hidastaa tietokantahakua. Kaikkein eniten hakua kuitenkin hidastaa se, että SQL-kyselyssä on käytössä haku LIKE '%hakusana%'. Käytännössä tietokannan koko sisältä skannataan läpi jokaisella haulla, eikä indeksejä tai muita nopeuttavia apuja juuri käytetä.

Suosittu ratkaisu: Relevanssi

Suomalaisen Mikko Saaren kehittämä WordPress-lisäosa Relevanssi on erittäin suosittu, koska se tekee sisällöstä etukäteen indeksejä eli yksinkertaistettuna aakkostettuja listoja. Jos loppukäyttäjän hakusana on vaikkapa ”peruna”, löytyy se indeksistä nopeasti kirjaimen ”P” kohdalta. Relevanssin asetuksista voi myös räätälöidä sivuston haun toimintaa monella tavalla.

Haasteena Relevanssissa on se, että koska kaikki sisältö indeksoidaan, paisuttaa se väkisinkin tietokantaa merkittävästi. Yhdessä testissämme WordPress-sivustolla, jolla oli yhden gigatavun kokoinen wp_posts-taulu, tuli Relevanssin indeksien kooksi viisi gigatavua. Haku näin isosta indeksistä ei ollut enää kovin nopeaa, vaikka se oli silti vähintäänkin tuplasti nopeampi WordPressin vakiohakuun verrattuna.

Usein mainittu ratkaisu: Elastic Search

Yksi vaihtoehto on ulkoistaa indeksit kokonaan ulos WordPressistä. Blogeissa paljon näkyvyyttä viime aikoina saanut Elastic Search perustuu juuri tähän. Ideana on kopioida kaikki sisältö WordPressin tietokannasta (joka on aina MariaDB tai MySQL) ja viedä se Elastic Searchiin, joka on oma tietokantaohjelmistonsa.

Viennin yhteydessä tehdään indeksoinnit ja muut toimenpiteet, jonka jälkeen Elastic Searchin avulla voi tehdä nopeasti hakuja. Vientiä varten pitää WordPressiin asentaa lisäosa, esimerkiksi Fantastic ElasticSearch. Elastic Searchin itse puolestaan vaatii oman tietokantapalvelimensa.

Tällainen kokonaisuus voi olla houkutteleva vaihtoehto kehittäjälle, joka mielellään virittää räätälöityjä ratkaisuja. Se on kuitenkin sangen työläs siitä saatavaan hyötyyn nähden. Elastic Search -tietokantapalvelimen ja integraatiot voi ostaa myös palveluna, jolloin kokonaisuus on helpompi ottaa käyttöön ja hallinoida, mutta toisaalta palvelusta joutuu maksamaan vähintään 75 euroa kuukaudessa.

Haku palveluna

Jos on valmis ostamaan haun palveluna, on vaihtoehtoja useita, kuten WordPressille räätälöity SearchWP tai yleiskäyttöisempi Algolia. Hintaa näille palveluille kertyy kymmeniä tai jopa satoja euroja kuukaudessa. Täysin ilmainen vaihtoehto on yksinkertaisesti käyttää Googlen hakukonetta omalla sivustolla. Räätälöidyn haun voi luoda Google Custom Search -palvelun avulla.

Seravon ratkaisu: FULLTEXT-indeksi

MariaDB:n InnoDB-tietokantamoottorista löytyy sisäänrakennettuna oma indeksityyppi, FULLTEXT INDEX. Sitä hyödyntämällä WordPressin tietokannasta ei tarvitse viedä tietoja ulos toiseen tietokantaan, tai monistaa tietoja uusiin tauluihin: indeksoinnin voi tehdä suoraan siihen tauluun, jossa tiedot jo ovat. Tietojen päivittyessä tietokannassa vastaava FULLTEXT-indeksi myös päivittyy automaattisesti ja ilman viivettä. Tehokasta.

Käyttöönotto

Uuden indeksin käyttöönotto wp_posts-taulun sisällölle tapahtuu komennolla:

ALTER TABLE wp_posts
ADD FULLTEXT post_title_content_fulltext
(post_title, post_excerpt, post_content);

Indeksin luonti kestää taulun koosta riippuen muutaman sekunnin tai minuutin. Eräässä tapauksessa, jossa wp_posts-taulun koko oli noin gigatavu, indeksin syntyi Seravon tuotantopalvelimella noin neljässä minuutissa. Sisällöstä riippuen FULLTEXT-indeksin koko on noin 70% indeksoitavasta sisällöstä eli levytila kasvaa kertoimella 0,7, mikä on melko vähän esim Relevanssiin verrattuna.

Indeksin toimivuutta voi testata ajamalla käsin SQL-hakuja tyyliin:

SELECT ID,post_title FROM wp_posts
WHERE MATCH(post_title, post_excerpt, post_content)
AGAINST('+tasapainoa +horjuttaa' IN BOOLEAN MODE)
LIMIT 0,10;
SELECT ID,post_title, MATCH(post_title, post_excerpt, post_content) AGAINST('tasapainoa horjuttaa') as relevance FROM wp_posts
WHERE MATCH(post_title, post_content)
AGAINST('tasapainoa horjuttaa')
LIMIT 0,10;
SELECT ID,post_title, MATCH(post_title, post_excerpt, post_content) AGAINST('tasapainoa horjuttaa') as relevance
FROM wp_posts
WHERE MATCH(post_title, post_content)
AGAINST('tasapainoa ei saa horjuttaa')
LIMIT 0,10;
SELECT ID,post_title, MATCH(post_title, post_excerpt, post_content) AGAINST('"tasapainoa ei saa horjuttaa"') as relevance
FROM wp_posts
WHERE MATCH(post_title, post_content)
AGAINST('"tasapainoa ei saa horjuttaa"')
LIMIT 0,10;
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID,wp_posts.post_type,
MATCH(post_title, post_excerpt, post_content) AGAINST('"tasapainoa ei saa horjuttaa"') as relevance
FROM wp_posts
WHERE 1=1
AND wp_posts.ID NOT IN (23027,22432,960,8909,18865,23778)
AND MATCH(post_title, post_excerpt, post_content) AGAINST('"tasapainoa ei saa horjuttaa"')
ORDER BY relevance DESC, wp_posts.post_date DESC
LIMIT 0, 10

Integrointi WordPressiin

WordPressin sisäänrakennettua hakua voi muuttaa posts_search-hookin avulla. Alla olevan koodiesimerkin voi lisätä vaikkapa sivustolla käytössä olevan teeman functions.php-tiedostoon:

function mariadb_fulltext_search($sql, $query) {
  global $wpdb;
  $keyword = $query->get('s');
  if ( ! $keyword || $sql === '' ) {
    return $sql;
  }
  $sql = " AND MATCH ({$wpdb->posts}.post_title, {$wpdb->posts}.post_excerpt," .
         " {$wpdb->posts}.post_content) AGAINST ('%s') ";
  return $wpdb->prepare($sql, $keyword);
}
add_filter('posts_search', 'mariadb_fulltext_search', 12, 2);

Käyttöönoton voi myös tilata Seravolta asiantuntijatyönä, jolloin voimme hoitaa asian 1-2 h työtunnissa asiakkaamme puolesta.

Käyttöönotto lisäosan avulla: Relevanssi Light

Relevanssin luojan Mikko Saaren ja Seravon toimitusjohtajan Otto Kekäläisen lounastapaamisesta loppuvuodesta 2019 sai alkunsa yllä olevaa tekniikkaa hyödyntävä, vasta julkaistu lisäosa Relevanssi Light.