Ufdjrw

I have a MySQL table in which 1000s of posts are indexed as follows

First column contains the keyword, second, the number of occurrences and in the 3rd column there is an array in which each index contains an array of length 3 such that 1st index represents the id of the post, 2nd the number of occurrences in that post and 3rd index contains an array of line numbers in which the keyword is found.

Now I want to create a search engine where users can search for posts based on keywords. The most basic solution would be to fetch all the results for each keyword from a mysql query and then find all the common post ids using javascript (as I am using Node).

I know there must be ways for making this efficient. I think of using MongoDB, but I am unsure whether it will work.
And also I cannot use elastic search.

For normalization, you'd probably want at least two tables.

One for just holding the keywords (and an easily and quickly referenced keyword_id value).

A second table for the "index", holding (keyword_id, post_id, line_number). The existence of fields holding counts becomes a bit redundant when simple "count" queries on this index table can yield the same results without needing to be kept synchronized with string data.

Then to find posts with your keywords, you can just do a query like this:

SELECT i.post_id, COUNT(DISTINCT i.keyword_id) AS keywordsUsed 
FROM keywords AS k
INNER JOIN keywords_index AS i ON k.keyword_id = i.keyword_id
WHERE k.keyword IN ( 'your', 'list', 'of', 'keywords')
GROUP BY i.post_id
ORDER BY keywordsUsed DESC
;

or this

SELECT post_id, COUNT(DISTINCT keyword_id) AS keywordsUsed 
FROM keywords_index 
WHERE keyword_id IN (
 SELECT keyword_id 
 FROM keywords 
 WHERE keyword IN ( 'your', 'list', 'of', 'keywords')
)
GROUP BY post_id
ORDER BY keywordsUsed DESC
;

Another thing worth keeping in mind is that while this table feels much bigger (lots more rows), it will probably take up much less actual space (and be faster to access because of it):

The string [[113, 1, [822]], [199, 1, [11592]],[267, 1, [5293 is a minimum of 50 bytes (assuming single byte character set), not counting length specifier for the string itself. Even removing the count values and associated commas and spaces only reduces the data by 9 bytes.

113, 822
199, 11592
267, 5293
is 24 bytes, assuming INT is used for the id values.

Select query should be something like this:

Select third column name from table name where first column name like %?%

so if you pass in agree you should get [[2,1,[673], [7,3,[303, 2863, 2866],[9,2,[...]]

This should return your array which you should be able to map() over

if you want the first index...

array.map((subArray) => subArray[0]); //returns an array of the first index

this would return [2,7,9]

If you are trying to get the third index and combine them you should be able to do a foreach() and concat()

let sample = [];
array.forEach((subArray) => sample = sample.concat(subArray););

This would return `[673, 303, 2863, 2866, ...]