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?

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.

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 ?

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

@tsingh777 whats the status on this?

Need to run some performance tests. will update soon.