Hot answers tagged

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


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

In addition to what Justin Cave already wrote about PostgreSQL, there is a new feature in PostgreSQL 9.1 to speed up any search with LIKE (~~) or ILIKE (~~*). You can now 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: ...


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

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


17

No, that's pretty much what they're doing. Now, if there is not a leading wildcard and the field is indexed, which is the usual situation, the database engine can apply the regular expression to the index. So, for example, if you write SELECT * FROM employees WHERE last_name LIKE 'Cav%' the database can use the index on LAST_NAME to find all the rows ...


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

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


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

You can use the INFORMATION_SCHEMA database and the COLUMNS table in particular Example of use: SELECT table_name, column_name, data_type, ordinal_position FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'myDatabase' --- the database you want to search AND column_name = 'name' ; --- or: column_name LIKE '%name%'


9

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


9

The schema change it taking so long because you are assigning a default value to the column during the change and enforcing that with a non-nullable column, and it has to populate the column for 60+ million rows, which is an incredibly expensive operation. I'm not sure what your application requirements are but an approach that would make the schema change ...


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

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


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


8

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


8

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


8

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


8

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


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

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


7

The per language noise list (also referred to as stop list) and thesaurus files are in the MSSQL/FTData/ folder. The noise files are plain text, the thesaurus XML, so both can be inspected or changed easily. Configuring Full-Text Linguistic Components in BOL has the details.


7

I have an interesting surprise for you. The only Optimizing for FullText Indexing you can do is not something at the my.cnf level. It is all about two things: The Stopword List The Query STOPWORDS There are 543 stopwords that you may or may not want filtered out of FULLTEXT indexes. The list of stopwords was built at compile time. You can override that ...


7

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: http://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index The short version is that the user interface does not ...


6

I found out that even if it is not installed it is still available on SQL Server Management Studio and you can create the indexes. It shouldn't allow... Then I found this: SQL Server 2008 R2 Express - Installation Options, to allow FTS I need the 800MB version... To check the status of FTS I found these queries: SELECT FULLTEXTCATALOGPROPERTY(cat....


6

Full Text Search features are available in Microsoft SQL Server 2005 Express Edition with Advanced Services Microsoft® SQL Server® 2008 Express with Advanced Services Installation and configuration are pretty simple, hope you have already installed express edition. Give it a try! UPDATE: 2011 - MARCH - 08 You will not be able manage Full Text ...


6

Keith, Full-text catalog had a complete architecture change in 2008 & above and that's why this option is removed and NOT necessary. Definitely NOT the answer you were looking for :-) Ref: http://msdn.microsoft.com/en-us/library/ms189520(v=SQL.100).aspx Important Beginning with SQL Server 2008, a full-text catalog is a virtual object and ...


6

Previous to SQL Server 2012 the ADD COLUMN NOT NULL DEFAULT ... is an offline operation as it has to run an update and populate each row with the new default value of the newly added column. In SQL Server 2012 the operation is much faster, see Online non-NULL with values column add in SQL Server 11 as it only updates the metadata of the table and does not ...


6

To find a list of tables that have a FULLTEXT INDEX SELECT 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 sys.fulltext_index_columns ic ON ...


6

This is not really a use case for full text search because full text relies on stemming the text and parsing the chunks into tokens. As you can see from keywords, '580h' is parsed as its own word because there's no language in which '580' is a "stem" of '580h'. You'd probably be better off with regular expression matching. Here's a query that I worked up ...



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