There is a request to make the SO search default to an AND style functionality over the current OR when multiple terms are used. The official response was: not as simple as it sounds; we use SQL Server 2005's FREETEXT() function, and I can't find a way to specify AND vs. OR -- can you? So, is there a way? There are a number of re...
Can anyone explain this behavior or how to get around it? If you execute this query: select * from TblA left join freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key] inner join DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID It will be very very very slow. If you change that query to use two inner joins instead of a left joi...
Is it possible to order results in SQL Server 2005 by the relevance of a freetext match? In MySQL you can use the (roughly equivalent) MATCH function in the ORDER BY section, but I haven't found any equivalence in SQL Server. From the MySQL docs: For each row in the table, MATCH() returns a relevance value; that is, a similarity mea...
There is a store procedure that uses FREETEXTTABLE twice on two tables and then merges the results and returns the top 50. The problem is if I do a search on "Women of Brewster", the results returns "Confession of an ex doofus motha" with a rank of 143 from table A and second "Women of Brewster Place" with a rank of 102 from table B. ...
I have a web page that allows users to search a Question & Answer database table (no its not StackOverflow). The web page accepts the search term from the user and passes it to a stored procedure as a parameter on a SQL Server 2005 database. That sproc does a SELECT on the table in question, using FREETEXT in the WHERE clause to search...
The following FreeTextTable query takes > 10 seconds on the first request, but about a second for subsequent requests: SELECT [Key], [Rank] INTO #matches FROM FREETEXTTABLE(Book, [Description], @searchWord) After approx. 10 minutes of inactivity the next request will once again take > 10 seconds. This seems like the freetext cache is ...
I'm writing a search query in SQL Server 2005 for a discussion board. The proc takes several parameters, but most are "optional". There's one search field for Message Body, which I have a full text index on. Here's the problem.. If I pass in a value to search against with FreeText, the search works fine (thank you Microsoft). Howeve...
OK this is the situation.. I am enabling fulltext search on a table but it only works on some fields.. CREATE FULLTEXT CATALOG [defaultcatalog] CREATE UNIQUE INDEX ui_staticid on static(id) CREATE FULLTEXT INDEX ON static(title_gr LANGUAGE 19,title_en,description_gr LANGUAGE 19,description_en) KEY INDEX staticid ON [defaultcatalog] WIT...
Hello, Our site makes use of a web based, rich-text editor FreeTextBox. In IE, not FF, if a user types in something like: someone@blah IE automatically creates a mailto hyperlink. I have tested this with other text editors out there and the story is the same with all of them. So the question can I override this browser behavior s...
Hi, Assuming the text is typed at the same time in the same (Israeli) timezone, The following free text lines are equivalent: Wed Sep 9 16:26:57 IDT 2009 2009-09-09 16:26:57 16:26:57 September 9th, 16:26:57 Is there a python module that would convert all these text-dates to an (identical) datetime.datetime instance? I would like to...
AFAIK,MySQL performs really bad at this, what's your solution? BTW,what's the solution of SO? EDIT Please pay attention that free-text search itself is pretty fast in MySQL, but not the case when the result also needs to be sorted on an attribute! ...
I have two tables: **Product** ID Name SKU **Brand** ID Name Product table has about 120K records Brand table has 30K records I need to find count of all the products with name and brand matching a specific keyword. I use freetext 'contains' like this: SELECT count(*) FROM Product inner join Brand on Product.B...
I seem to have a weird bug in Microsoft SQL Server 2005 where FREETEXT() searches are somewhat case-sensitive despite the collation being case-insensitive (Latin1_General_CI_AS). First of, LIKE queries are perfectly case-insensitive, so WHERE column LIKE '%word%' and WHERE column LIKE '%Word%' return the same results. Also, FREE...
I have a webhosting at Godaddy with MS-SQL, I have a table called Product with 3 columns (Id, Title, Details) I want to implement Freetext or Contains function to search multiple words. When I execute the query: Select * from Product where freetext(Title,'Car Honda') It answers me "Cannot use a CONTAINS or FREETEXT predicate on table...
I am having a problem with a FreeText search, currently running on a SQL 2000 server. In a table of approximately 1.3 million rows which contain company names, I am attempting to use a FreeText query. However since SQL Server strips out special characters and single characters when building its index, our code does the same when subm...
The Product table has 700K records in it. The query: SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc takes about 1 minute to run. There is an non-clustered index on DateMadeNew and FreeText index on Name. If I remove TOP 1 or Order By - it takes less then 1 second to run...
I have a query with about 6-7 joined tables and a FREETEXT() predicate on 6 columns of the base table in the where. Now, this query worked fine (in under 2 seconds) for the last year and practically remained unchanged (i tried old versions and the problem persists) So today, all of a sudden, the same query takes around 1-1.5 minutes. ...
I am running a small website wherein I would like to make a functionality like Related Links section. For that I created fulltext catalog and index. So far I tried many ways to create search query which behaves like google, but I would say i was not even 10% close to what google is doing. During my research what i found was only follo...
Hey, Currently, we've got an application that needs to perform very fast searches on ~2 mill records. Searches need to search both a large free-text field, and a number of integer/decimal fields between different ranges, along with various functions/computations & sorting. Currently, we're handling this with a big MSSQL database, usin...
Currently in the middle of building a knowledge base app and am a bit unsure on the best way to store and index the document information. The user uploads the document and when doing so selects a number of options from dropdown lists (such as category,topic,area..., note these are not all mandatory) they also enter some keywords and a d...