3

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?

Nicos
  • 303
  • 2
  • 19

4 Answers4

5

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • With your karma rating you probably don't need random people to tell you that you're clever, but this is very clever. – octern May 08 '12 at 21:57
1

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.

nickb
  • 59,313
  • 13
  • 108
  • 143
  • 1
    I was just writing the same thing. Only, selecting (explicitly) the word column instead of * is faster. And yes - make sure that the column is indexed. – Narf May 08 '12 at 21:26
  • Thank you nick. I've tried all the solutions that others provided, but yours was the simplest and faster and mostly, i am using just 1 query! – Nicos May 09 '12 at 07:08
1

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) . '"

DonCallisto
  • 29,419
  • 9
  • 72
  • 100
  • That's kind of amusing, reversing the logic like that. Instead of testing whether the word is in the database, you test whether the words of the database are in the list of permutations. For long words, you might have to increase `max_allowed_packet` as described in http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html -- this query could easily grow to multiple MB in size. – octern May 08 '12 at 21:54
  • @octern : yes if you won't to do `n` query, you have to do something like that, or do a `select` of all words, loads it into memory onto PHP side and check, into the returned array, if there's one of the permutated words. – DonCallisto May 08 '12 at 22:00
  • In case it wasn't clear, I was saying that I like your solution. – octern May 08 '12 at 22:03
  • @octern : yes, i noticed that but i also explain why i think that this is almost the unique good solution :) – DonCallisto May 08 '12 at 22:04
0

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.

Del Pedro
  • 1,216
  • 12
  • 32