FullText indexes in multiple variable columns
up vote
1
down vote
favorite
I am currently looking into using FULLTEXT indexes in MySQL for search functionality within a web site.
Basically, the user can go to an advanced search page, and select 1 or more columns to search against, e.g. they can search Title, Description and Comments or either only 1 column or a mixture of the three and when they perform the search these selected columns are searched for against the keywords.
I had created 1 index for the title, 1 index for the description and 1 index for the comments and then tried to run the following query:
SELECT * FROM support_calls WHERE MATCH(Title, Description) AGAINST('+these, +are, +some, +keywords')
I got an error from MySQL saying that the MATCH didn't match any fulltext indexes and I found that I need to create an index which included Title and Description together instead of having them in separate indexes.
This is going to add some complexity if this is the case as I am going to have to create an index for every single variation of what columns the user selects. Am I going about this the right away or is there a better solution?
mysql full-text-indexing
edited Nov 10 at 17:56
halfer
14.2k757104
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
add a comment |
up vote
1
down vote
favorite
I am currently looking into using FULLTEXT indexes in MySQL for search functionality within a web site.
Basically, the user can go to an advanced search page, and select 1 or more columns to search against, e.g. they can search Title, Description and Comments or either only 1 column or a mixture of the three and when they perform the search these selected columns are searched for against the keywords.
I had created 1 index for the title, 1 index for the description and 1 index for the comments and then tried to run the following query:
SELECT * FROM support_calls WHERE MATCH(Title, Description) AGAINST('+these, +are, +some, +keywords')
I got an error from MySQL saying that the MATCH didn't match any fulltext indexes and I found that I need to create an index which included Title and Description together instead of having them in separate indexes.
This is going to add some complexity if this is the case as I am going to have to create an index for every single variation of what columns the user selects. Am I going about this the right away or is there a better solution?
mysql full-text-indexing
edited Nov 10 at 17:56
halfer
14.2k757104
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
AFAIK that's a MySQL restriction you can't override. Depending on your needs, you may be able toMATCH(Title) AGAINST('+these, +are, +some, +keywords') OR MATCH(Description) AGAINST('+these, +are, +some, +keywords')and use ranks to sort or further filtering.
– Álvaro González
Jan 13 '16 at 12:08
This seems to work from the test I've done, I'm a bit worried it may get quite slow but so far so good. Could you make this an answer and I'll accept it
– Boardy
Jan 13 '16 at 14:05
I'm far from being an expert in full text and I don't want to post a potentially misleading or plain wrong answer. For instance, I've just leant that index requirements are different depending on storage engine and boolean/natural mode ref.
– Álvaro González
Jan 13 '16 at 17:10
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am currently looking into using FULLTEXT indexes in MySQL for search functionality within a web site.
Basically, the user can go to an advanced search page, and select 1 or more columns to search against, e.g. they can search Title, Description and Comments or either only 1 column or a mixture of the three and when they perform the search these selected columns are searched for against the keywords.
I had created 1 index for the title, 1 index for the description and 1 index for the comments and then tried to run the following query:
SELECT * FROM support_calls WHERE MATCH(Title, Description) AGAINST('+these, +are, +some, +keywords')
I got an error from MySQL saying that the MATCH didn't match any fulltext indexes and I found that I need to create an index which included Title and Description together instead of having them in separate indexes.
This is going to add some complexity if this is the case as I am going to have to create an index for every single variation of what columns the user selects. Am I going about this the right away or is there a better solution?
mysql full-text-indexing
edited Nov 10 at 17:56
halfer
14.2k757104
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
I am currently looking into using FULLTEXT indexes in MySQL for search functionality within a web site.
Basically, the user can go to an advanced search page, and select 1 or more columns to search against, e.g. they can search Title, Description and Comments or either only 1 column or a mixture of the three and when they perform the search these selected columns are searched for against the keywords.
I had created 1 index for the title, 1 index for the description and 1 index for the comments and then tried to run the following query:
SELECT * FROM support_calls WHERE MATCH(Title, Description) AGAINST('+these, +are, +some, +keywords')
I got an error from MySQL saying that the MATCH didn't match any fulltext indexes and I found that I need to create an index which included Title and Description together instead of having them in separate indexes.
This is going to add some complexity if this is the case as I am going to have to create an index for every single variation of what columns the user selects. Am I going about this the right away or is there a better solution?
mysql full-text-indexing
mysql full-text-indexing
edited Nov 10 at 17:56
halfer
14.2k757104
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
edited Nov 10 at 17:56
halfer
14.2k757104
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
edited Nov 10 at 17:56
halfer
14.2k757104
edited Nov 10 at 17:56
halfer
14.2k757104
edited Nov 10 at 17:56
halfer
14.2k757104
14.2k757104
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
asked Jan 13 '16 at 12:03
Boardy
14.1k79205357
14.1k79205357
AFAIK that's a MySQL restriction you can't override. Depending on your needs, you may be able toMATCH(Title) AGAINST('+these, +are, +some, +keywords') OR MATCH(Description) AGAINST('+these, +are, +some, +keywords')and use ranks to sort or further filtering.
– Álvaro González
Jan 13 '16 at 12:08
This seems to work from the test I've done, I'm a bit worried it may get quite slow but so far so good. Could you make this an answer and I'll accept it
– Boardy
Jan 13 '16 at 14:05
I'm far from being an expert in full text and I don't want to post a potentially misleading or plain wrong answer. For instance, I've just leant that index requirements are different depending on storage engine and boolean/natural mode ref.
– Álvaro González
Jan 13 '16 at 17:10
add a comment |
AFAIK that's a MySQL restriction you can't override. Depending on your needs, you may be able toMATCH(Title) AGAINST('+these, +are, +some, +keywords') OR MATCH(Description) AGAINST('+these, +are, +some, +keywords')and use ranks to sort or further filtering.
– Álvaro González
Jan 13 '16 at 12:08
This seems to work from the test I've done, I'm a bit worried it may get quite slow but so far so good. Could you make this an answer and I'll accept it
– Boardy
Jan 13 '16 at 14:05
I'm far from being an expert in full text and I don't want to post a potentially misleading or plain wrong answer. For instance, I've just leant that index requirements are different depending on storage engine and boolean/natural mode ref.
– Álvaro González
Jan 13 '16 at 17:10
AFAIK that's a MySQL restriction you can't override. Depending on your needs, you may be able to
MATCH(Title) AGAINST('+these, +are, +some, +keywords') OR MATCH(Description) AGAINST('+these, +are, +some, +keywords') and use ranks to sort or further filtering.– Álvaro González
Jan 13 '16 at 12:08
AFAIK that's a MySQL restriction you can't override. Depending on your needs, you may be able to
MATCH(Title) AGAINST('+these, +are, +some, +keywords') OR MATCH(Description) AGAINST('+these, +are, +some, +keywords') and use ranks to sort or further filtering.– Álvaro González
Jan 13 '16 at 12:08
This seems to work from the test I've done, I'm a bit worried it may get quite slow but so far so good. Could you make this an answer and I'll accept it
– Boardy
Jan 13 '16 at 14:05
This seems to work from the test I've done, I'm a bit worried it may get quite slow but so far so good. Could you make this an answer and I'll accept it
– Boardy
Jan 13 '16 at 14:05
I'm far from being an expert in full text and I don't want to post a potentially misleading or plain wrong answer. For instance, I've just leant that index requirements are different depending on storage engine and boolean/natural mode ref.
– Álvaro González
Jan 13 '16 at 17:10
I'm far from being an expert in full text and I don't want to post a potentially misleading or plain wrong answer. For instance, I've just leant that index requirements are different depending on storage engine and boolean/natural mode ref.
– Álvaro González
Jan 13 '16 at 17:10
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
first execute below query and then run your MATCH() query.
ALTER TABLE support_calls ADD FULLTEXT (
Title, Description
)
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
first execute below query and then run your MATCH() query.
ALTER TABLE support_calls ADD FULLTEXT (
Title, Description
)
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
add a comment |
up vote
0
down vote
first execute below query and then run your MATCH() query.
ALTER TABLE support_calls ADD FULLTEXT (
Title, Description
)
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
add a comment |
up vote
0
down vote
up vote
0
down vote
first execute below query and then run your MATCH() query.
ALTER TABLE support_calls ADD FULLTEXT (
Title, Description
)
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
first execute below query and then run your MATCH() query.
ALTER TABLE support_calls ADD FULLTEXT (
Title, Description
)
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
answered Jan 13 '16 at 12:25
Rakesh Sojitra
2,1621721
2,1621721
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
add a comment |
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
The OP already acknowledges that. The question is about an alternative to creating a new index for every possible column combination.
– Álvaro González
Jan 13 '16 at 12:27
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
you can also use like %word% or any other wild card characters instead of MATCH
– Rakesh Sojitra
Jan 13 '16 at 12:34
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f34766239%2ffulltext-indexes-in-multiple-variable-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
AFAIK that's a MySQL restriction you can't override. Depending on your needs, you may be able to
MATCH(Title) AGAINST('+these, +are, +some, +keywords') OR MATCH(Description) AGAINST('+these, +are, +some, +keywords')and use ranks to sort or further filtering.– Álvaro González
Jan 13 '16 at 12:08
This seems to work from the test I've done, I'm a bit worried it may get quite slow but so far so good. Could you make this an answer and I'll accept it
– Boardy
Jan 13 '16 at 14:05
I'm far from being an expert in full text and I don't want to post a potentially misleading or plain wrong answer. For instance, I've just leant that index requirements are different depending on storage engine and boolean/natural mode ref.
– Álvaro González
Jan 13 '16 at 17:10