Search code examples
mysqlwordnet

Wordnet MySQL statement doesn't complete

I'm using the Wordnet SQL database from here: http://wnsqlbuilder.sourceforge.net

It's all built fine and users with appropriate privileges have been set.

I'm trying to find synonyms of words and have tried to use the two example statements at the bottom of this page: http://wnsqlbuilder.sourceforge.net/sql-links.html

SELECT synsetid,dest.lemma,SUBSTRING(src.definition FROM 1 FOR 60) FROM wordsXsensesXsynsets AS src INNER JOIN wordsXsensesXsynsets AS dest USING(synsetid) WHERE src.lemma = 'option' AND dest.lemma <> 'option'

SELECT synsetid,lemma,SUBSTRING(definition FROM 1 FOR 60) FROM wordsXsensesXsynsets WHERE synsetid IN ( SELECT synsetid FROM wordsXsensesXsynsets WHERE lemma = 'option') AND lemma <> 'option' ORDER BY synsetid

However, they never complete. At least not in any reasonable amount of time and I have had to cancel all of the queries. All other queries seem to work find and when I break up the second SQL example, I can get the individual parts to work and complete in reasonable times (about 0.40 seconds)

When I try and run the full statement however, the MySQL command line client just hangs.

Is there a problem with this syntax? What is causing it to take so long?

EDIT:

Output of "EXPLAIN SELECT ..."

Explain Select Output

Output of "EXPLAIN EXTENDED ...; SHOW WARNINGS;"

Show Warnings Output

Solution

  • I did more digging and looking into the various statements used and found the problem was in the IN command.

    MySQL repeats the statement for every single row in the database. This is the cause of the hang, as it had to run through hundreds of thousands of records.

    My remedy to this was to split the command into two separate database calls first getting the synsets, and then dynamically creating a bound SQL string to look for the words in the synsets.