Tag Info

Hot answers tagged

30

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


19

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


11

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


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

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


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.


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

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


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

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


6

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

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


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


5

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


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

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


5

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


5

Found beta of a product that does this because I was asked the question directly. Not affiliated with this company is any way. See "How To Return Document Property Values in Full-Text Search" at: http://www.sqlskills.com/blogs/bobb/.


5

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


5

Your comments note that you're specifically talking about full-text indexing. You can indeed query the database while a full-text index is being created. Full text indexes are created in the background on SQL Server 2005, 2008, and 2008R2. You can continue to query the database using the LIKE operator, although of course the queries won't be as fast as a ...


5

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.


5

When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast) The second time you run the query, it is likely that data and index pages for the relational tables are coming from SQL Server's cache rather than being read in from disk. Likewise, the full-text ...



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