Tag Info

New answers tagged

0

I suggest a MATERIALIZED VIEW with unnested values and a trigram index as search tools. hstore If you don't need nested values, hstore may be even better for you. Use the function svals(hstore) to unnest hstore values. You need to install the additional module hstore once per database: CREATE EXTENSION hstore; Key value pair in PostgreSQL Table ...


1

I wasn't able to find any good resources online, so I did some more hands-on research and thought it would be useful to post the resulting full-text maintenance plan we are implementing based on that research. Our heuristic to determine when maintenance is needed Our primary goal is to retain consistent full-text query performance as data evolves in the ...


0

In place of your query, try this: SELECT * FROM dbo.Analysis where FREETEXT(dbo.Analysis, 'historic year-on-year exchange rates forecast yearon-year exchange rates'); or this: SELECT * FROM dbo.Analysis where CONTAINS(dbo.Analysis, "historic year-on-year exchange rates forecast year-on-year exchange rates"); Also, the hyphens may be causing ...


0

In parts similar to my last answer: SELECT a.id, a.article_title AS title, a.pub_date, a.web_id c.city_points, c.city_names FROM article a LEFT JOIN ( SELECT ac.article_id AS id , string_agg(st_asText(c.geom), ', ') AS city_points , string_agg(c.city, ', ') AS city_names FROM article_cities ac JOIN city c ON ...


1

The blog post you refer to is wrong in using [INNER] JOIN between post and posts_tags. Nothing enforces that at least one tag exists, so posts without tags are excluded from the result. (Also, why is there no PK for posts_tags? A dubious DB design.) You copied that error and made it much worse by combining multiple relationships that way. Each many-to-many ...


4

As Martin Smith commented, "It depends." The factors that control the processing of Full Text indexes depends on the configuration you use in your CREATE FULLTEXT INDEX command. Language - Which controls the parsing of words and phrases according to that language's rules. Stop Word List - Words to be left out of the search, though their position in the ...


0

If you are using InnoDB FULLTEXT indexes, queries will often hang in the "FULLTEXT initialization" state if you are querying against a table that has a large number of deleted rows. In InnoDB's FULLTEXT implementation, deleted rows are not pruned until a subsequent OPTIMIZE operation is run against the affected table. See: ...


0

Unclear what you want to do. If you want to see rows with keyword in both d and somewhere in a,b,c, then your AND is a good way to go. But if you want keyword to be in any of a,b,c,d, then add a third index FULLTEXT(a,b,c,d) and change to MATCH(a,b,c,d) AGAINST('keyword') For further discussion please specify whether you are using InnoDB or MyISAM; ...



Top 50 recent answers are included