#226 Improve Kosh searching results order

[tsingh777]

Followed this example: https://www.codexworld.com/how-to/sort-results-order-by-best-match-using-like-in-mysql/

The order would be 1 - if the query matches the words 2 - if the query matches the beginning of the word 3 - if the query matches the end of the word 4 - if the query matches the definition 5 - if the query matches the beginning of the definition 6 - if the query matches the end of the definition

Also adds a new end point that searches only on words and not definitions.

[ManjotS]

Adding an if then in a query is a performance killer and not scalable (in any database).

On Mon, Jan 4, 2021 at 7:52 AM tsingh777 [email protected] wrote:

@tsingh777 commented on this pull request.

In api/controllers/kosh.js https://github.com/KhalisFoundation/banidb-api/pull/227#discussion_r551399908 :

•                WHEN wordUni LIKE ? THEN 2
  

•                WHEN word LIKE ? THEN 3
  

•                WHEN wordUni LIKE ? THEN 3
  

•                ELSE 4
  

•              END`;
  

• const rows = await conn.query(q, [

•  match,
  

•  match,
  

•  match,
  

•  match,
  

•  fullMatch,
  

•  fullMatch,
  

•  startMatch,
  

•  startMatch,
  

•  endMatch,
  

•  endMatch,
  

Will prioritize #113 https://github.com/KhalisFoundation/banidb-api/issues/113 next so these things can be caught earlier.

— You are receiving this because your review was requested. Reply to this email directly, view it on GitHub https://github.com/KhalisFoundation/banidb-api/pull/227#discussion_r551399908, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAE5OFODGI3QK4XQVJNQ3FTSYHP25ANCNFSM4VSFTE4A .

[tsingh777]

Adding an if then in a query is a performance killer and not scalable (in any database). … On Mon, Jan 4, 2021 at 7:52 AM tsingh777 @.> wrote: @.* commented on this pull request. ------------------------------ In api/controllers/kosh.js <#227 (comment)> : > + WHEN wordUni LIKE ? THEN 2 + WHEN word LIKE ? THEN 3 + WHEN wordUni LIKE ? THEN 3 + ELSE 4 + END`; + const rows = await conn.query(q, [ + match, + match, + match, + match, + fullMatch, + fullMatch, + startMatch, + startMatch, + endMatch, + endMatch, Will prioritize #113 <#113> next so these things can be caught earlier. — You are receiving this because your review was requested. Reply to this email directly, view it on GitHub <#227 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAE5OFODGI3QK4XQVJNQ3FTSYHP25ANCNFSM4VSFTE4A .

@ManjotS What do you recommend?

[tarunsingh5]

Adding an if then in a query is a performance killer and not scalable (in any database). … On Mon, Jan 4, 2021 at 7:52 AM tsingh777 @.> wrote: @.* commented on this pull request. ------------------------------ In api/controllers/kosh.js <#227 (comment)> : > + WHEN wordUni LIKE ? THEN 2 + WHEN word LIKE ? THEN 3 + WHEN wordUni LIKE ? THEN 3 + ELSE 4 + END`; + const rows = await conn.query(q, [ + match, + match, + match, + match, + fullMatch, + fullMatch, + startMatch, + startMatch, + endMatch, + endMatch, Will prioritize #113 <#113> next so these things can be caught earlier. — You are receiving this because your review was requested. Reply to this email directly, view it on GitHub <#227 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAE5OFODGI3QK4XQVJNQ3FTSYHP25ANCNFSM4VSFTE4A .

there isn't a logical work around - what you could do is either pre-stage that data by setting up intermediary tables or you can reduce the # of conditions you are checking for.

[tsingh777]

What about UNION ?

SELECT w.ID AS id, w.Word AS word, w.WordUni AS wordUni,
                  d.DefGurmukhi AS definition, d.DefGurmukhiUni AS definitionUni
                FROM MahanKoshWords w
                LEFT JOIN MahanKoshDefinitions d ON w.Definition = d.ID
                WHERE w.Word LIKE ? 
UNION 
SELECT w.ID AS id, w.Word AS word, w.WordUni AS wordUni,
                  d.DefGurmukhi AS definition, d.DefGurmukhiUni AS definitionUni
                FROM MahanKoshWords w
                LEFT JOIN MahanKoshDefinitions d ON w.Definition = d.ID
                WHERE w.WordUni LIKE BINARY ?
UNION 
SELECT w.ID AS id, w.Word AS word, w.WordUni AS wordUni,
                  d.DefGurmukhi AS definition, d.DefGurmukhiUni AS definitionUni
                FROM MahanKoshWords w
                LEFT JOIN MahanKoshDefinitions d ON w.Definition = d.ID
                WHERE d.DefGurmukhi LIKE ?
UNION 
SELECT w.ID AS id, w.Word AS word, w.WordUni AS wordUni,
                  d.DefGurmukhi AS definition, d.DefGurmukhiUni AS definitionUni
                FROM MahanKoshWords w
                LEFT JOIN MahanKoshDefinitions d ON w.Definition = d.ID
                WHERE d.DefGurmukhiUni LIKE BINARY ?

[tarunsingh5]

this should work, another thing to make this faster may be to do the where clause prior to the join

[AkalUstat]

@tsingh777 whats the status on this?

[tsingh777]

Need to run some performance tests. will update soon.