How to Query Records that Have Same Words but Maybe Different Words Order?

[dealmaker]

Hi,
I am trying to find a way to do a select query that can find records that have same words in any orders. e.g. "hello good morning", "good hello morning", "morning good hello"... they should all returned by the select query.

Please don't tell me that I can use "or" inside select query. It's because what if the string has 10 words? There are tons of permutations.

Many thanks.

[hyperbole]

Try using LIKE

SELECT fields
FROM table
WHERE field LIKE "%good%"
AND field LIKE "%morning%"
AND field LIKE "%hello%";




.

[dealmaker]

But it will match record that contains "good morning hello sir", which is not what I want. And I think "LIKE" is slow in mysql.

Try using LIKE

SELECT fields
FROM table
WHERE field LIKE "%good%"
AND field LIKE "%morning%"
AND field LIKE "%hello%";




.

[Velox Letum]

If the query with OR's on it is too slow, maybe try adding an index for the keyword field.

[dealmaker]

I don't understand, can you be more specific?

If the query with OR's on it is too slow, maybe try adding an index for the keyword field.

[Velox Letum]

Well if you add OR for every keyword, it has to check all of the rows resulting in a slower query, but if you specify an index for the keyword field then it'll be sped up by a very large magnitude...sometimes even 100-1000 times faster.

[dealmaker]

So if the phase I am search has 10 words, like "good morning sir hello how are you today hi" (ignore double quotes), it will be better to use all premutations with "or" in select query than using "LIKE" or Fulltext search even if there are millions of rows after indexing?

e.g. select a, b from c where d = "good morning sir hello how are you today hi" or d = "morning good sir hello how are you today hi" or d = "morning sir good hello how are you today hi" ........

is faster than

using LIKE or FULLTEXT search even if there are millions of rows?

Well if you add OR for every keyword, it has to check all of the rows resulting in a slower query, but if you specify an index for the keyword field then it'll be sped up by a very large magnitude...sometimes even 100-1000 times faster.

[GJay]

Could you maybe give the reason you want to do this? It strikes me as a very odd request, that either a different data-structure, or a completely different solution, might could help...

[dealmaker]

Ok, I am having keywords that I used as search queries to search for pages. e.g. good morning america. And I have to keep statistics about them. And I assume that "america morning good" and "morning america good" produces same result and statistics. I keep all these variations in the same row in database, so I don't have to save 6 different variations of "good morning america" in 6 different rows in database. But the only way to do that is to be able to retrieve the record of "good morning america" when the search query is for "morning good america". It can't match row that has "good morning america hello"
Do you have any good recommendation?

Could you maybe give the reason you want to do this? It strikes me as a very odd request, that either a different data-structure, or a completely different solution, might could help...

[GJay]

Store them in the database in aplhabetical order, then when the search is done, order the search terms alphabetically and you don't need to worry about the permutations.

[Velox Letum]

Use an index on the field and it'll also help.