Re^2: Faster of two options for finding keywords in a database

Or possibly more simply just keep a database lookup table with columns:

keyword || id_reference_to_text_column

then query that table with your keywords like "select id_reference_to_text_column from lookup_table where keyword in (key1, key2, ...)" or however you do that in postgres. Then you could simply sum up the occurrences of each id_reference_to_text_column.

Or faster with group by (mysql syntax)

create table tmp (kwd varchar(255), idx integer, primary key (kwd, idx))

populate table ... then query it like

select idx, count(*) from tmp where kwd in ("heugh", "stook", "tanti")
+ group by idx
[download]

Comment on Re^2: Faster of two options for finding keywords in a database Select or Download Code

Replies are listed 'Best First'.
Re^3: Faster of two options for finding keywords in a database by NERDVANA (Priest) on Feb 18, 2024 at 03:46 UTC
This is at least cross-platform, but it's also sort of what the postgres fulltext feature does. The postgres fulltext should still run faster though, and I think it uses much more compact storage than creating one row per keyword per original object with a b-tree on the whole thing. Also debatable which is "simpler", because with a table of keyword-to-record-id you have to maintain that yourself. With the one I showed, once you get the table and query set up, postgres does all the work to maintain the index.	[reply]

Replies are listed 'Best First'.

Re^3: Faster of two options for finding keywords in a database
by NERDVANA (Priest) on Feb 18, 2024 at 03:46 UTC

Also debatable which is "simpler", because with a table of keyword-to-record-id you have to maintain that yourself. With the one I showed, once you get the table and query set up, postgres does all the work to maintain the index.

[reply]