Jump to content

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


Recommended Posts

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).  :-\

Link to comment
Share on other sites

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));

Link to comment
Share on other sites

  • 10 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.