Tag Info

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

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


14

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


12

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.


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


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

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


8

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


8

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


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


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

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


7

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


6

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


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

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


6

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


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

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


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


6

Its a two step process. In the installer select Maintenance/Edition Upgrade to upgrade your existing instance. Run the installer again and select Installation/...or add features to an existing installation/Add features to an existing instance.


6

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


6

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


5

Speaking about MySQL, the position of the wild-card character (%) makes a difference. If the first part of the text is specified like where first_name like 'Sta%', then the DB engine will search only a smaller subset of words staring with S, then going to St, and then Sta, etc. If you do something like where first_name like '%stan%', then and entire scan of ...


5

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


5

Probably not The view that may display native stored compiled indexes is CTX_INDEX_VALUES Or CTX_USER_INDEX_VALUES (depending on permissions and querying user). However, given that the index value length is only 500 characters, it is unlikely that it is stored there. (Just start selecting randomly from those indexes, if it exists, it will likely be there.) ...


5

I did some more research and found a couple of useful articles with best practices regarding table partitioning and full-text searching on large tables. As I haven't received any answers to this question, I thought I'd post what I've found here for future reference. I'm still reading those, but for what I've already read, I've found both of them useful and ...



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