Take the 2-minute tour ×

It's a confusion problem - so the wording may seem hard to follow. I'm likely over complicating a simple problem. Added an example question to help figure this out.

Example Question

Finding a 5 letter word from a database with the characters hxinenvarbav. I've organized the words in the DB to also contain a column of the word in alphabetical format. This means, the word "happy" has a column with the value "ahppy", so from the letters hxinenvarbav I can alphabetically organize them using the following code.

    $letters = str_split('hxinenvarbav'); asort($letters);
    $letters = implode('',$letters); // returns 'aabehinnrvvx'

The Issue

However, I can't simply search with mysql "LIKE '%aabehinnrvvx%' " and find 5 letter words with those characters, as obviously that will not pull any results. Unless maybe there is a MySQL query I could do? Maybe organize the column differently. I can however, use str_split($letters,5) to take 5 letters chunks of the 12 letter combination.

How would I go about, splitting in chunks each possible 5 letter combination from these 12 letters while keeping in mind, I need to query the table.

Does this make sense? Do I need to elaborate any further? Likely, I'm just over thinking and can't seem to simplify what it is I'm trying to accomplish. I have some complex mathematics that can find all possible combinations. But since I have placed in alphabetical order, I'm only searching combinations - not permutations. And on top of that, I don't need to as far as I logically believe, query 'each' combination. As there are 792 possible 5 letter combinations from only 12 letters (without calculating repeating characters). So 792 query calls is not nice - and 792 OR statements in my query, is clearly not an option. LOL!.

Any suggestions?

I did just think about searching via available characters left from alphabet - but, some words have repeating letters so that's not an option either.

share|improve this question
Have you looked into regex? –  Jeremy Feb 25 '13 at 3:12
A regex would need to be formatted to search for 5 letter chunks. I can't fathom a way to do an expression that separates 12 letters into 5 letter chunks in one query. Is that possible? –  James Cordeiro Feb 25 '13 at 3:27
The only other option might be to index the letter counts for each word. Fastest search for ASCII encoding may be 26 int fields. –  user1389596 Feb 25 '13 at 3:34
LOL, be careful when suggesting that any option might be the "only" other option. –  phatfingers Feb 25 '13 at 3:36

2 Answers 2

If you have a table named "dict" that contains fields "word", and "combo", where "combo" contains the alphabetized characters of each "word", then you can index on "combo".

You programmatically build your set of combinations in memory, and use it to build a SELECT statement based on each combination using IN clause, like "SELECT * FROM dict WHERE combo IN ('combo1', 'combo2', ..., 'comboN');".

Should be pretty fast and simple to implement.

share|improve this answer
But if someone is searching 6 letter combinations from a 12 letter possible, that's a query with 924 combo's - seems lengthy to me. Especially if I expand to a 26 letter combination possibility in the future. Could turn into a query with 1 million plus combinations. No longer a fast and simple query –  James Cordeiro Feb 25 '13 at 3:37
I just mean at most 26 OR statements. Its ugly but would be faster than a join or regex –  user1389596 Feb 25 '13 at 3:44
Ugly - but it would work. I'll work on this for a bit. If I find a better solution, I'll let you know @user1389596 .. I'll work with that for now and see what I can work out this evening. Thanks dude. –  James Cordeiro Feb 25 '13 at 3:49
@JamesCordeiro if your SQL statement exceeds some sort of limit, be it number of characters in the statement itself, or number of elements in an IN clause, you can still break it into multiple statements. –  phatfingers Feb 25 '13 at 3:49
@user1389596 I'm intrigued that both of you see an optimization, but I'm not fully understanding your solution. If 12 characters are given and all valid 6-character words are desired, then what would your query look like? –  phatfingers Feb 25 '13 at 3:59

You are likely not to accomplish this with only one SQL statement, but a combination of SQL and post query filtering for performance reasons.

select * from A where word_len = 5 and (
 substring( word_in_db, 1, 1) IN ('h', 'x', 'i', 'n', 'e', 'n', 'v', 'a', 'r', 'b', 'a', 'v')
-- etc...

This will limit the amount of clauses to the amount of letters in your target word.

This won't find duplicates like a word that has 2 Es but input letters with only 1 E. You will probably want to compute the word length and save that as a derived value for speed (and index the column, of course).

share|improve this answer
You might want to sort the letters in the DB not in alphabetical order, but in order of their commonality, putting letters like z and q at the beginning and vowels and common letters (like "r,s,t,l,n,e") at the end. This will short circuit the where clause as soon as possible and save many many substr calls. –  chugadie Feb 26 '13 at 14:37

Your Answer


