Settings for fastest query?

  • Resolved ctrlaltdelete

    (@ctrlaltdelete)


    7 years, 4 months ago

    I was wondering what are the best settings to get a the fastest results?
    I only need to search titles, not content or tags or fields.
    I need AND logic. Dates is fine no relevance needed.

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Mikko Saari

    (@msaari)

    7 years, 4 months ago

    If you only want titles in the search, disable the post content with these instructions. That’ll make the indexing very fast and will also improve search performance.

    If you have enough posts, make sure throttle is enabled (Relevanssi will tell you if the throttle is unnecessary).

    Sorting by relevance is actually faster than sorting by date, because Relevanssi generates relevance-sorted results and if you want any other order, it’ll have to resort the posts.

    Thread Starter ctrlaltdelete

    (@ctrlaltdelete)

    7 years, 4 months ago

    Alright so throttle ON, sort by relevance and add the hook to skip content.
    I’ve done the first 2 but the article says the hook only exist on the premium version of the plugin, i’m on the free so it would have no effect for me?

    Also does it cache popular search query results? I feel like it’s doing that because sometimes it takes literally no time to find results while other times it’s about 0.9 secs.

    And if i create a new post does it get added to the index automatically? No need to re-index myself each time right? (This could be obvious just making sure)

    Thanks!

    • This reply was modified 7 years, 4 months ago by ctrlaltdelete.
    Plugin Author Mikko Saari

    (@msaari)

    7 years, 4 months ago

    No, the hook is available in both free and Premium.

    Relevanssi doesn’t do any caching, but your server may (and should).

    Yeah, Relevanssi handles updating the index automatically.

    Thread Starter ctrlaltdelete

    (@ctrlaltdelete)

    7 years, 4 months ago

    Ok i implemented it. The difference isn’t dramatic but i think it helped a bit. Would you consider adding options to disable parts of the query? For example if i only want to search title (due to big data searching more is a slow query)
    I’d like to completely eliminate these from the query:

    relevanssi.comment
    relevanssi.tag
    relevanssi.link
    relevanssi.author
    relevanssi.category
    relevanssi.excerpt
    relevanssi.taxonomy
    relevanssi.customfield
    relevanssi.mysqlcolumn (dunno what this is)
    relevanssi.content (emptied with the hook, but still on query somehow)

    Basically i just want to search on relevanssi.title with an AND logic and that’s it.
    You might think well, use WP native search and limit it to titles. And i did but Relevanssi index is much faster than searching WP tables.

    I know i’m asking too much but worth a shot.
    Thanks for everything.

    PS: Maybe something like you already have with weights, if weight is 0 don’t add it to the query. Would that work?

    • This reply was modified 7 years, 4 months ago by ctrlaltdelete.
    Plugin Author Mikko Saari

    (@msaari)

    7 years, 4 months ago

    Yeah, the query doesn’t change, just what’s in the database, but that’s not the slow part.

    On my test server, which runs on Local by Flywheel on my desktop computer, doing a search query takes 0.008 seconds to perform. I tried removing the unnecessary bits from the query, ran it again, and it took 0.009 seconds to run.

    I did another attempt at a production site with a bigger database, and there strippping the unnecessary bits made a difference: running the query took only 0.081 seconds instead of 0.414 seconds.

    While that is a real difference, my view is that if you’re concerned by differences in this scale, Relevanssi is not the tool you should be using in the first place. If speed is an issue at this level, you’re wasting your time with Relevanssi, and should be using something like LucidWorks Site Search, Algolia or Swiftype. Those are much, much faster than Relevanssi, and there’s no way Relevanssi can compete with them.

    But if you want to convince me, run some tests on your data.

    The basic Relevanssi search query looks something like this:

    SELECT DISTINCT(relevanssi.doc), relevanssi.*, relevanssi.title * 5 + relevanssi.content * 1 + relevanssi.comment * 1 + relevanssi.tag * 1 + relevanssi.link * 1 + relevanssi.author + relevanssi.category * 1 + relevanssi.excerpt + relevanssi.taxonomy + relevanssi.customfield + relevanssi.mysqlcolumn AS tf 
FROM wp_relevanssi AS relevanssi 
WHERE (relevanssi.term LIKE 'lorem%'
OR relevanssi.term_reverse LIKE CONCAT(REVERSE('lorem'), '%'))

    Replace both lorem with a word that’s common in your database, run this and note the time it takes. Then run this query:

    SELECT DISTINCT(relevanssi.doc), relevanssi.*, relevanssi.title * 5 AS tf 
FROM wp_relevanssi AS relevanssi 
WHERE (relevanssi.term LIKE 'lorem%'
OR relevanssi.term_reverse LIKE CONCAT(REVERSE('lorem'), '%'))

    with the same search term. How big a difference there is? If, with your data, you get a big enough difference, I’ll promise to consider changing this.

    Thread Starter ctrlaltdelete

    (@ctrlaltdelete)

    7 years, 4 months ago

    In my test (used same word for both lorem) the first query was actually faster 0.5 while the second took 0.9. Weird and doesn’t help my case but that’s the truth.

    So i guess your plugin is already great =D

    I’m trying to get rid of anything above 0.1 (i know, pretty strict) and a great deal comes from search since it’s a heavy task. I’m not gonna go into detail because it doesn’t help you but i got a cheap VPS (€2.5/mo) and installed my own elasticsearch server. It’s a lot cheaper than going with a hosted SAAS i mean some are asking astronomical figures for something that doesn’t really need that much hardware, and elasticsearch is a free software…seems to me they are abusing their knowledge a bit.
    I mean i have 16k posts, if i were to use swiftype i would have to pay them $2000/mo. How insane is that? elasticsearch DB is tiny and disk space is dirt cheap. Charging by document is wrong. They assume many documents = Lots of traffic = CPU/memory usage. It doesn’t. Oh well let’s leave it at that.

    Stay well and sorry for wasting your time with my babbling!!!

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘Settings for fastest query?’ is closed to new replies.