Support MySQL/MariaDB as backend?

[nemiah]

Hi,

storing the data in a SQLite-file on the disk is a performance problem in my use case (GlusterFS) and I'd prefer using MySQL/MariaDB as backend.

Are you interested in that feature in general and would you support me, if I'd implement it?

Best wishes
Nena

[daun]

@nemiah That sounds like a good use case for something like Litestream.

The upcoming release of Litestream will let you live-replicate SQLite directly between databases, which means you can set up a write-leader database with distributed read replicas. Read replicas can catch writes and redirect them to the leader; most applications are read-heavy, and this setup gives those applications a globally scalable database.

[Toflar]

I honestly don't think that it is possible to make Loupe compatible with any other relational database other than SQLite. I have built Loupe with SQLite because SQLite has one very important advantage: It allows to register custom functions which we need. The entire ranking algorithm cannot be represented using typical regular relational database functions.
If you'd wanted that, it's very likely that you would need special stored procedures and what not. Which again makes setup harder and the whole goal of Loupe is to not require anything special. No special plugins, no fancy setup or additional extensions etc.
Because I think the moment we do that, there are 0 arguments against "why not just install Meilisearch right away?". If you need to install anything anyway, you might as well just install MS - it will be faster, more accurate and more powerful so I don't see why anybody would not want this 😊

[nemiah]

Thank you for your answer!

I took a quick look at Meilisearch and I would not use it, because I have a multi-tenant cloud application, which just needs better search. My application already uses PHP and MySQL and I do not want to add another tech stack. I would be willing to do a little bit more setup to avoid having some external software I need to understand and maintain. I don't need AI-powered. I just need typo-resilient search.
This is why I'm taking the time to write you. Loupe fits my use-case.

Before I give up, I'd ask you to please elaborate on "The entire ranking algorithm cannot be represented using typical regular relational database functions.". Why do you think that?

[Toflar]

Because I'm fairly confident that there is no way you can represent what the custom loupe_relevance() query function is doing now :) Did you already look into that part?

[nemiah]

I have not so far, because it did not show up.
What does the query need to look like, that it is used?

[Toflar]

The SQL is pretty complex, with CTEs and custom functions such as loupe_max_levenshtein() as well as loupe_relevance() which expect a very custom result.
This is an example from our tests:

WITH _cte_term_matches_0 (id) AS (
  SELECT 
    t.id 
  FROM 
    terms t 
  WHERE 
    (t.term = : dcValue1)
), 
_cte_term_matches_1 (id) AS (
  SELECT 
    t.id 
  FROM 
    terms t 
  WHERE 
    (
      t.term = : dcValue2 
      OR t.state IN (6907, 27632) 
      AND t.length >= 7 
      AND t.length <= 9 
      AND loupe_max_levenshtein(: dcValue3, t.term, 1, true)
    ) 
    OR (
      t.term = : dcValue4 
      OR t.state IN (107, 431, 443, 1726) 
      AND t.length >= 4 
      AND t.length <= 6 
      AND loupe_max_levenshtein(: dcValue5, t.term, 1, true)
    ) 
    OR (t.term LIKE : dcValue6)
), 
_cte_term_document_matches_0 (
  document, term, attribute, position
) AS (
  SELECT 
    td.document, 
    td.term, 
    td.attribute, 
    td.position 
  FROM 
    terms_documents td 
  WHERE 
    td.term IN (
      SELECT 
        id 
      FROM 
        _cte_term_matches_0
    ) 
  ORDER BY 
    position
), 
_cte_term_document_matches_1 (
  document, term, attribute, position
) AS (
  SELECT 
    td.document, 
    td.term, 
    td.attribute, 
    td.position 
  FROM 
    terms_documents td 
  WHERE 
    td.term IN (
      SELECT 
        id 
      FROM 
        _cte_term_matches_1
    ) 
  ORDER BY 
    position
) 
SELECT 
  COUNT() OVER() AS totalHits, 
  d.document, 
  loupe_relevance(
    (
      SELECT 
        group_concat(_relevance_per_term, ';') 
      FROM 
        (
          SELECT 
            (
              SELECT 
                COALESCE(
                  group_concat(
                    DISTINCT position || ':' || attribute
                  ), 
                  '0'
                ) 
              FROM 
                _cte_term_document_matches_0 
              WHERE 
                d.id = document
            ) AS _relevance_per_term 
          UNION ALL 
          SELECT 
            (
              SELECT 
                COALESCE(
                  group_concat(
                    DISTINCT position || ':' || attribute
                  ), 
                  '0'
                ) 
              FROM 
                _cte_term_document_matches_1 
              WHERE 
                d.id = document
            ) AS _relevance_per_term
        )
    ), 
    2, 
    'content:1'
  ) AS _relevance 
FROM 
  documents d 
WHERE 
  (
    (
      d.id IN (
        SELECT 
          DISTINCT document 
        FROM 
          _cte_term_document_matches_0
      )
    ) 
    OR (
      d.id IN (
        SELECT 
          DISTINCT document 
        FROM 
          _cte_term_document_matches_1
      )
    )
  ) 
ORDER BY 
  _relevance DESC 
LIMIT 
  20

Params:

 array:6 [
  "dcValue1" => "life"
  "dcValue2" => "learning"
  "dcValue3" => "learning"
  "dcValue4" => "learn"
  "dcValue5" => "learn"
  "dcValue6" => "learning%"
]

[nemiah]

Thank you, I'll see if I get the tests to work…