74

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


24

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:*') ...


18

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


18

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.


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


15

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


15

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


14

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


11

This isn't an official list, but using a loop to work through a list of characters, and using sys.dm_fts_parser like so: declare @i integer declare @cnt integer set @i=0 while @i<255 begin set @cnt=0 select @cnt=COUNT(1) FROM sys.dm_fts_parser ('"word1'+REPLACE(CHAR(@i),'"','""')+'word2"', 1033, 0, 0) if @cnt>1 begin print 'this char - '+...


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

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

Absolutely !!! Just run ALTER TABLE tblname ENGINE=MyISAM; against all tables on the Slave that you want to have the FULLTEXT index. Afterwards, you can run ALTER TABLE tblname ADD FULLTEXT (column[,column]);. Please be very careful not to run DDL against those tables in the Master that are unique to InnoDB that will replicate to the Slave. I have suggested ...


10

It looks like (at this time) the best you are going to be able to do is use the keywords on the property, join them up to the doc and cross your fingers it is enough. SELECT keyword, display_term, column_id, document_id, property_id FROM sys.dm_fts_index_keywords_by_property ( DB_ID('FileTableDB'), OBJECT_ID('FileTableTb') ); MSDN on ...


10

To find a list of tables that have a FULLTEXT INDEX: SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, c.name AS FTCatalogName , f.name AS FileGroupName, i.name AS UniqueIdxName, cl.name AS ColumnName FROM sys.tables t INNER JOIN sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id] INNER JOIN ...


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

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.


9

In addition to what @swasheck already explained, you'll probably get better performance with LIKE (~~) and ILIKE (~~*) in combination with a trigram GiST or GIN index. You'll have to install the additional module pg_trgm for that. Find details under these related questions: How is LIKE implemented? Pattern matching with LIKE, SIMILAR TO or regular ...


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

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

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

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

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


8

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


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


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

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


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