$query = “(SELECT content, title, ‘msg’ as type FROM messages WHERE content LIKE ‘%” . $keyword . “%’ OR title LIKE ‘%” . $keyword .”%’) UNION (SELECT content, title, ‘topic’ as type FROM topics WHERE content LIKE ‘%” . $keyword . “%’ OR title LIKE ‘%” . $keyword .”%’) UNION (SELECT content, title, ‘comment’ as … Read more
Workaround only for leading wildcard: store the text reversed in a different field (or in materialised view) create a full text index on this column find the reversed text with an * SELECT * FROM TABLENAME WHERE CONTAINS(TextColumnREV, ‘”mrethcraes*”‘); Of course there are many drawbacks, just for quick workaround… Not to mention CONTAINSTABLE…
If it is just for searching then you may be able to use CONCATENATE_WS. This would allow wild card searching. There may be performance issues depending on the size of the table. SELECT * FROM pages WHERE CONCAT_WS(”, column1, column2, column3) LIKE ‘%keyword%’
Yes. However you have to create SQL server function first and call that as by default LINQ will use a like. This blog post which will explain the detail but this is the extract: To get it working you need to create a table valued function that does nothing more than a CONTAINSTABLE query based … Read more
— drop table testproduct; CREATE TABLE testproduct ( Id VARCHAR(16), prod_name TEXT, ProductIdType VARCHAR(8), PRIMARY KEY (Id), FULLTEXT (prod_name) ) ENGINE=MyISAM; insert into testproduct (id,prod_name,productidtype) values (‘B00005N5PF’,’one pen and a good price for a pen’,’ASIN’); insert into testproduct (id,prod_name,productidtype) values (‘B570J5XS3C’,null,’ASIN’); insert into testproduct (id,prod_name,productidtype) values (‘C00ZZ5N5PF’,’let us get rid of some noise’,’ASIN’); insert into … Read more
Along with the general phasing out of MyISAM, InnoDB full-text search (FTS) is finally available in MySQL 5.6.4 release. Lots of juicy details at https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html. While other engines have lots of different features, this one is InnoDB, so it’s native (which means there’s an upgrade path), and that makes it a worthwhile option.
This might give the increased relevance to the head part that you want. It won’t double it, but it might possibly good enough for your sake: SELECT pages.*, MATCH (head, body) AGAINST (‘some words’) AS relevance, MATCH (head) AGAINST (‘some words’) AS title_relevance FROM pages WHERE MATCH (head, body) AGAINST (‘some words’) ORDER BY title_relevance … Read more
I’ve been using Solr successfully for almost 2 years now, and have never used Sphinx, so I’m obviously biased. However, I’ll try to keep it objective by quoting the docs or other people. I’ll also take patches to my answer 🙂 Similarities: Both Solr and Sphinx satisfy all of your requirements. They’re fast and designed … Read more
I’ll try to explain with a real example since the answer and replies you got don’t seem to help you. When you download elasticsearch and start it up, you create an elasticsearch node which tries to join an existing cluster if available or creates a new one. Let’s say you created your own new cluster … Read more