How long does it take to execute a while loop on 400,000+ records?

[sqlnoob]

Hi all! I have this question about performance and speed issues.

 

I have this sql table containing 2 columns. The first column contains an id, and the second column a word.

The entire table has about 416,500 words (apparently there are that many words in the Frisian language).

 

I have constructed a simple sql query, with a WHERE clause and a LIKE clause that searches for a patron. The goal is to search for words that rhyme with a user inputted word (it could be any kind of word used in the Frisian language). This site is used to aide people who want to write Frisian poetry, Santa Claus poetry to be more specific.

 

Now I have this php script that can detect which word actually rhymes, so that's not the problem. The real problem is that I worry about how long it will take for the script to loop through the sql table and find all the words that rhyme for instance with a word like "piet".

 

How long will it take to fetch say 300 records with a while loop from a table containing about 400,000+ words?

 

And if this takes a long time, are there ways to enhance performance? And if so how?

 

 

 

By the way, I have yet to find out what kind of sql database they use. The boss apparently doesn't know and she's the only one I've spoken to. So it could be that I ask this question in the wrong subforum, and they're using a different sql dialect. If that is the case my apologies. Anyhow I asked it here assuming, it is mssql they're actually using (but I could be wrong).  :-\

[sqlnoob]

I think, I've found some strategies to this "Gordian knot", namely parallel queries, but I don't know how much time that will buy me.

[Pikachu2000]

How long did the query take when you tried running it?

[ignace]

You could create a procedure in MySQL for it so you only get the records that actually rhymes with the word instead of finding a word that rhymes in PHP. If that sounds too difficult you could try mysql_unbuffered_query which, unlike mysql_query, does not store all 400k+ records in-memory.

 

Create a lookup table that stores all rhymes when one is found (word_id, rhymes_with_id) to speed up future searches.

 

Currently you have used an auto_increment as a primary key maybe you could convert every word to an integer (since it's a limited set although you could get a collision so backup before you do).

 

// for a 32-bit signed integer, use 15 if you are running PHP under x64 architecture (and use a BIGINT in MySQL)
$word_id = hex2int(substr(md5($word), 0, 7));

 

Or in MySQL using:

 

UPDATE words SET word_id = conv(substr(md5(word), 1, 7), 16, 10)

 

You don't need MySQL then to find the ID of the word as you can calculate it's ID in PHP (saving a round-trip).

 

$word_id = hex2int(substr(md5($_POST['search_word']), 0, 7));

[sqlnoob]

actually i just put the rows next to eachother that contain words that have the same combination of letters on the end. This allowed me to fetch the records according to ID number. It chaves seconds of the time it takes to run the query.