62

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


36

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


34

Pigeons. The heart of Google's search technology is PigeonRank™, a system for ranking web pages developed by Google founders Larry Page and Sergey Brin at Stanford University: Building upon the breakthrough work of B. F. Skinner, Page and Brin reasoned that low cost pigeon clusters (PCs) could be used to compute the relative value of web pages faster ...


34

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


26

In addition to what Justin Cave wrote, since PostgreSQL 9.1 you can speed up any search with LIKE (~~) or ILIKE (~~*), and basic regular expression matches, too (~). Use the operator classes provided by the module pg_trgm with a GIN or GiST index to speed up LIKE expressions that are not left-anchored. To install the extension, run once per database: CREATE ...


22

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

I am sure there is a combination of things: serious hardware lots of it - data is distributed and replicated across many nodes and different data centers (actually in the Google case at least I believe they have thousands and thousands of really low-end servers) a lot of common queries' results are cached, notice how they pre-populate potential searches ...


19

It's important to bear in mind a couple of things about google: Their DB is the proprietary BigTable - it was custom designed BY GOOGLE to exactly fit their needs Their proprietary DB is built on top of their proprietary file system - Google File System - this was designed, again BY GOOGLE, to be easily expandable using common commodity hardware. As Aaron ...


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


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


14

In SSMS Tools Options "SQL Server Object Explorer" "Script full text catalogs" Default is "false"...


14

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

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.


13

I took the three strings in your question and added it to a table plus three more string with pankt instead of punkt. The following was executed using MySQL 5.5.12 for Windows mysql> CREATE TABLE artikel -> ( -> id INT NOT NULL AUTO_INCREMENT, -> meldungstext MEDIUMTEXT, -> PRIMARY KEY (id), -> FULLTEXT ...


13

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


12

My guess is that this would fix your query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2(city)) @@ to_tsquery('simple',unaccent2('wroclaw')) ORDER BY to_tsvector('simple',unaccent2(city)) @@ to_tsquery('simple',unaccent2('wroclaw')) DESC ,displaycount LIMIT 20; I repeat the WHERE condition as first element ...


12

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


11

Google does not use traditional relational database technology. It developed its own technology, big table and map reduce. The original research papers are here : Big Table and Map/Reduce. Also of interest is the SSTable, sorted string table. Similar tech is now used in hadoop and the NoSQL databases.


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

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


10

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


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


9

Read Steven Levy's "In The Plex: How Google Thinks, Works, and Shapes Our Lives". This book is a fascinating read about all things Google and does discuss at a high level some of the technology and engineering behind search. Aaron sums it up really well in his answer and Levy's book will give you some more detail about how they do 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 ...


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