Tag Info

New answers tagged

1

Hmm I'm afraid '#' counts as punctuation and SQL Server Full-Text Indexing was invented before Twitter. There are however a couple of approaches: 1) Pre-processing Use the full-text functions to fetch most of the data then refine it with Like, eg SELECT Id INTO #tmp FROM dbo.Users WHERE CONTAINS ( Bio, '#promoter' ) SELECT * FROM dbo.Users u WHERE u.Bio ...


0

If you stop it (disable it), the index won't be available for queries using it. However you can set bchange tracking to manual: ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING MANUAL GO Update... ALTER FULLTEXT INDEX schema.table START UPDATE POPULATION GO ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING AUTO GO You can check the ...


0

Change the ft_min_word_len value to specify the minimum length of the word to be included in a MyISAM FULLTEXT index. ft_min_word_len The minimum length (default: 4) of the word to be included in a MyISAM FULLTEXT index. Note: FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK. ...


0

MATCH(...) AGAINST('+tommy' IN ...) AND ... LIKE '%tommy -%' That is, use FULLTEXT to do what it does best (search for words), then check the results against something more complex via LIKE or REGEXP. Granted, it will take some intelligence in your app code to construct the suitable combination of MATCH and [R]LIKE. But the result is fast and cover a ...


0

MySQL Documentation Under Natural Language Full-Text Searches Paragraph 1 By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to ...


0

PHP's mysql escape functions are probably not FULLTEXT aware. The MySQL Documentation says about the Double Quotes in Booelan Mode Fulltext Searching '"some words"' Find rows that contain the exact phrase “some words” (for example, rows that contain “some words of wisdom” but not “some noise words”). Note The “"” characters that enclose the ...


2

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


0

I have found a way to scam the query planner, it is a quite simple hack: SELECT * FROM (select id, title, label from table1 where (lower(unaccent(label) like lower(unaccent('%someword%')))) t1 WHERE (lower(lower(unaccent(label))) like lower(unaccent('%someword and some more%'))) Bitmap Heap Scan on table1 (cost=6749.11..7332.71 rows=1 width=212) (actual ...


0

MySQL FT search isn't that fast as we hope to be. I'd recommend Sphinx - http://sphinxsearch.com/ - it provides fast and relevant full-text search. If you are wondering how fast is Sphinx (from my experience), it can take few minutes to search 200M records with MySQL, but with Sphinx, less than 2 seconds, and better accuracy.


1

The optimizer rarely does anything useful OR constructs. What you can attempt to do is Get ids from each of the MATCHs UNION the results JOIN back to the necessary tables to get the desired field. Something like this: SELECT i2.*, c2.name AS category, ... FROM ( SELECT i.id FROM phppos_items AS i WHERE MATCH(...) ...


0

I would recommend to reduce the amount of occurrences in the inner query over phppos_items. A way to do that is to get only the heavier records using a subquery. I see that you are using LIMIT 20, so I assume that you need online 20 records from the overall. Documentation What you can do to do so, is to trim the working set using a condition over the match ...


1

You can find the words that will be used in the match with the sys.dm_fts_parser function in SQL Server. The Syntax sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity) I won't go in details for the parameters values, but the "lcid" is important. In the example below I'm using 1033 which is for English - US. I've tried with 1036, ...


1

I'm not super familiar with full text searches but I think this StackOverflow answer gives us the answer which is also supported by this StackOverflow answer. The solution was to create a computed column containing what is essentially a concatenation of all the columns you want to apply your search terms to and do your full text search on that column. ...


0

Found my stupid mistake... instead of running the query as my_table.objects.raw(sql_query) I was creating a connection to another db with conn = psycopg2.connect("dbname='wrong_db' user='my_user' password='my_password'") cur = conn.cursor() cur.execute(sql_query) so lesson learnt... use the Django built-in capabilities to eliminate the possibility of a ...


3

I am curious about what you will accomplish by getting the plain text. You already have the documents in the FileTable and you can open the files when needed using the appropriate tools. For example: If you are looking at a PDF, a Word document, an Excel spreadsheet, and so forth you probably have the tools to look at the data. Most tools will even allow ...


1

I've written the following script to install an en_us dictionary on Ubuntu 14.04 running PostgreSQL 9.4. It should be fairly easy to modify for most situations. #!/bin/bash cd /usr/share/postgresql/9.4/tsearch_data wget http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_US.dic wget ...



Top 50 recent answers are included