79

SUGGESTION #1 : Standard Indexing CREATE TABLE mytable ( id int not null auto_increment, myfield varchar(255) not null, primary key (id), key (myfield) ); If you index like this, you can either look for the whole string or do left-oriented LIKE searches SUGGESTION #2 : FULLTEXT Indexing CREATE TABLE mytable ( id int not null ...


37

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 ...


35

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it). What the oldest version does is search for each trigram in the query and take the ...


27

Do all queries have to be be in dictionary? No. Because only word stems (according to the used text search configuration) are in the index to begin with. But more importantly: No. Because, on top of that Full Text Search is also capable of prefix matching: This would work: SELECT id, subject FROM mailboxes WHERE tsv @@ to_tsquery('simple', 'avail:*') ...


21

PostgreSQL 10+ PostgreSQL 10 introduces Full Text Search on JSONB CREATE INDEX ON table USING gin ( to_tsvector('english',jsondata) ); The new FTS indexing on JSON works with phrase search and skips over both the JSON-markup and keys.


20

MySQL enables you to define prefixed index which means you define first N characters from original string to be indexed, and the trick is to choose a number N that’s long enough to give good selectivity, but short enough to save space. The prefix should be long enough to make the index nearly as useful as it would be if you’d indexed the whole column. ...


17

You forgot to mention that you installed the additional module pg_trgm, which provides the similarity() function. Similarity operator % First of all, whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And index support is bound to operators in Postgres, not to ...


17

Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns. Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use ...


16

For your kind of pattern matching you best use a trigram index. Read this first: How is LIKE implemented? I assume there's a typo in your expression (first_name || '' || last_name), which makes no sense with an empty string, and you really want (first_name || ' ' || last_name) - with a space character. Assuming that either column can be NULL, you would ...


16

You are looking at the wrong place. You have to check as below : Using T-SQL .. Use database_name go ALTER FULLTEXT INDEX ON schema.table_name SET CHANGE_TRACKING AUTO; Once done, you can check the status of the last populated datetime -- script source : http://stackoverflow.com/a/10505496/1387418 -- Modified by Kin on Dec 14' 2015 to reflect the ...


11

Questionable use case ...each CONTENT entry consists of one random word and a text string that is the same for all rows. A text string that is the same for all rows is just dead freight. Remove it and concatenate it in a view if you need to show it. Obviously, you are aware of that: Granted, it is not realistic ... But since I can't control the text ....


11

There are two typical ways to express this. With LIKE infix search: SELECT title FROM snippets WHERE description LIKE '%evil%'; or with position: SELECT title FROM snippets WHERE position('evil' in description) > 0; Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.


11

This is, I think, a common misconception when it comes to full text search in SQL Server. Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS documentation CONTAINS can search for: A word or phrase. The prefix of a word or phrase. A word near another word. A word inflectionally ...


11

Your execution plan When looking at the query plan, we can see that one index is touched to serve two filter operations. Very simply put, due to the TOP operator, a row goal was set. Much more information & prerequisites on row goals can be found here From that same source: A row goal strategy generally means favouring non-blocking navigational ...


10

There is already an existing answer posted by Aaron Bertrand for SQL Server Express 2012. First install SQL Server 2014 Express with Advanced Services as you did. Then read Aaron's instructions at: https://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index The short version is that the user interface does not ...


10

I suggest this expression for query and index: SELECT * FROM tbl WHERE to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode)) @@ plainto_tsquery('simple', '22 Kärntner Wien'); Note the custom function f_concat_ws() above. That's because concat_ws() is only STABLE not IMMUTABLE. You need to create it first: CREATE OR REPLACE ...


9

First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constraint to enforce uniqueness on (cid, name, synonym). If one of your varchar columns actually uses the maximum length you would exceed the maximum size for an index ...


9

Will/can Solr/Lucene searches be faster than PostgreSQL even if no full-text search is involved? Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really. Solr is a search engine. PostgreSQL is a relational database engine. Solr is built from the ground up to do one thing well, ...


9

It is difficult to answer the question specifically without a good description of what you are trying to achieve and why, but it appears that Full-Text Search may not be a good fit for you based on your need to search for characters that are either reserved for a special purpose within the full-text engine, treated as delimiters by word-breakers, or present ...


9

Assessment In your last query, the bitmap index scan looking for 'hat' produces 307 hits. Postgres then runs a bitmap heap scan to filter merchants similar enough ( similarity(...) > 0.2), producing 12 rows. Your test is with 30K rows, so your real life query will produce around 300 times as many hits, 90k / 3.5k for the test case at hand. An additional ...


9

Setting ft_min_word_len only affects MyISAM. You need to set innodb_ft_min_token_size to 1 since the default is 3. Once you set innodb_ft_min_token_size to 1, go back and do ALTER TABLE addresses DROP INDEX address_index CREATE FULLTEXT INDEX address_index ON addresses(street); Give it a Try !!!


9

The key word here is phrase search, introduced with Postgres 9.6. Use the tsquery FOLLOWED BY operator <-> or one of the related <N> operators. Or better yet, use the function phraseto_tsquery() to generate your tsquery. Quoting the manual, it ... produces tsquery that searches for a phrase, ignoring punctuation And: phraseto_tsquery behaves ...


8

This is documented although I couldn't find a reference in Books Online: The rules for characters followed by nonalphanumeric characters are somewhat convoluted (at least in English). The English word breaker accepts the token C# and returns C#. The lowercase token c#, however, is indexed as c with the # character stripped off. The uppercase token ...


8

Optimization You're going down the right track. You either need to Denormalize Cache Caching the results What you probably want is a MATERIALIZED VIEW. This is easy and works reasonably well. CREATE MATERIALIZED VIEW foo AS SELECT t.id, to_tsvector(concat_ws(' ',a.name, o.address, t.description, a.name)) AS tsv FROM tasks AS t INNER JOIN actors AS a ...


7

After a few days of fiddling around, I finally managed to get this done. Here is the code I ended up with, packed into a stored procedure. For this to work, I added another table, which just holds a document ID and its length in words. ALTER PROCEDURE [dbo].[BuildIndexBM25] -- default parameters K1=> [1.2 - 2.0], B => [0.0 - 1.0], Weight => 3 (e.g. ...


7

This example uses the Canadian English dictionary, but you can try it with others as well. These are the steps needed for Windows: Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic Select all of the text, copy it, and paste it to Text Mechanic: http://textmechanic.co/Sort-Text-Lines.html. Add a line break at the end. ...


7

I've written the following script to install an en_us dictionary on Ubuntu 14.04 running PostgreSQL 9.4. It should be fairly easy to modify for most situations. #!/bin/bash cd /usr/share/postgresql/9.4/tsearch_data wget http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_US.dic wget http://src.chromium.org/svn/trunk/deps/third_party/...


7

Reading the manual it does not seem to be anything bad. MySQL just informs you about an additional action it had to do. InnoDB uses a unique document identifier referred to as a Document ID (DOC_ID) to map words in the full-text index to document records where the word appears. The mapping requires an FTS_DOC_ID column on the indexed table. If ...


7

The key here is that Column_1, represents three possible values for the JOIN. So what you want to use is string_to_array() (so long as those values are comma-separated and can not themselves include a comma). Run this query, SELECT id_number, string_to_array(column_1, ',') AS column_1 FROM table_a; id_number | column_1 -----------+-----------...


7

The way I read this question, you only care about message. The difficulty here is that you need to, map over a json array returning the message element reduce/fold the array of message element strings to an aggregate string. This is easy in functional programming. It's not as easy with the stock functions in PostgreSQL, and it'd be difficult to make it ...


Only top voted, non community-wiki answers of a minimum length are eligible