Tag Info

New answers tagged

2

This is what I can come up with (untested). You have to be able to tell the server that you're searching for "AB" and "ED" separately - otherwise you will get a very large number of possible combinations very quickly. SELECT t1.*, t2.* FROM table1 t1, table2 t2 WHERE t1.t1_field LIKE '%AB%' OR t1.t2_field LIKE '%ED%'. If you require different substring ...


0

I can reproduce this but I think you have a few options: not worry too much about full-text index bloat. Not many people are going to be searching for 'rtf1', 'pard' or 'wmetafile0' and I don't think it will affect your performance that much. You might get the odd clash if someone is searching for 'red' or 'blue' but I'd say this is fairly low risk. ...


1

This can be improved in a thousand and one ways, then it should be a matter of milliseconds. Better Queries This is just reformatted with useful aliases and some noise removed to clear the fog: SELECT count(DISTINCT t.id) FROM tickets t JOIN transactions tr ON tr.objectid = t.id JOIN attachments a ON a.transactionid = tr.id WHERE t.status ...


2

Option 1 The planner has no insight into the true nature of the relationship between EffectiveId and id, and so probably thinks the the clause: main.EffectiveId = main.id is going to be much more selective than it actually is. If this is what I think it is, EffectiveID is almost always equal to main.id, but the planner doesn't know that. A possibly ...


0

You could try an implementation of one of the string distance functions. Here's a T-SQL implementation of the Levenshtein distance taken from here. That post does suggest there's a performance issue with the function and scalar functions generally can be bad for performance when used in queries, but worth a look. You could always try and convert it to a ...


1

Setting ft_min_word_len only affects MyISAM. You need to set innodb_ft_min_token_size to 1 since the default is 3. Once you set innodb_ft_min_token_size to 1, go back and do ALTER TABLE addresses DROP INDEX address_index CREATE FULLTEXT INDEX address_index ON addresses(street); Give it a Try !!!


0

I solved by doing the following: Using the original command of the post Running a command to change the credential of the services (using sc.exe) Rebooting so the services would start (calling sc start was not working, even if called multiple times, don't know if it's related to changing the service credentia


3

Fulltext Search is a specialized technology that complements a traditional SQL database. There is a simple full text indexing feature built into MySQL, but you will probably find other alternatives that are more featureful and higher performance. I did a presentation comparing them here: Full Text Search Throwdown. The short answer: Sphinx Search. Re ...


1

No is the short answer, and you don't really need this. Full-text indexes are inverted indexes so they store the split words by the unique doc_id that you have to specify when you create the full-text index. This must be a "unique, single-key, non-nullable column" ideally an integer. What is essentially a foreign key does not figure and there is no easy ...


2

PROBLEM From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column. Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on. SOLUTION What you really need is a single FULLTEXT index with all 3 columns ALTER TABLE ...



Top 50 recent answers are included