CODEWITHSUNDEEP
phpmysqlanagram
Nicos
Nicos

Reputation: 303

Mysql multiple queries for anagram solver

I have a simple but huge table called 'dictionary' which has 2 columns. Id and word. I have a php anagram algorithm tha creates all possible combinations of a given word. I want to chech if that word exists in my dictionary and if yes display it. But i have to use too many queries. For example a 9 letter word is 362880 combinations. Any idea of how to make fewer db calls?

Upvotes: 3

Views: 808

Answers (4)

Mark Byers
Mark Byers

Reputation: 838736

Try something like this:

SELECT word
FROM dictionary
WHERE word LIKE '%a%a%a%'
AND word LIKE '%n%'
AND word LIKE '%g%'
AND word LIKE '%r%'
AND word LIKE '%m%'
AND CHAR_LENGTH(word) = 7

Better still, store the sorted arrangement of letters along with the word, and sort the letters when querying.

SELECT word
FROM dictionary
WHERE sorted_word = 'aaagmnr'

Add an index on sorted_word for best performance.

Upvotes: 5

Del Pedro
Del Pedro

Reputation: 1213

I Don't know how big your table is, but if you have enough memory on the server and if you have to do this many times within a request - load the database into php and let php do it, maybe with an associative array.

EDIT: or: enter all your combinations into an array, split it to junk with a size of 100 and use prepared statements.

Upvotes: 0

DonCallisto
DonCallisto

Reputation: 29932

Yes, first of all make all permutation directly into php. Second, do a query like that

SELECT myWord FROM myTable
WHERE myWord in (LIST OF PERMUTATION FROM PHP)

Where LIST OF PERMUTATION could be computed in that way "' . implode( '", "', $permutations) . '"

Upvotes: 1

nickb
nickb

Reputation: 59709

Try something like this for only one query, although I don't know how efficient such a query will be:

$possibilities = array( "at", "ta");
$sql = 'SELECT * FROM dictionary_table 
            WHERE word IN ("' . implode( '", "', $possibilities) . '")';

This will generate the SQL query:

SELECT * FROM dictionary_table 
            WHERE word IN ("at", "ta")

The upside is that the query is dynamic, so no matter how many $possibilities there are, this would theoretically work. For efficiency, I would definitely index the word column.

Upvotes: 1

Related Questions