PlanetMysql.ru — информация о СУБД MySQL

Some days ago I was working in a vocabulary game and dictionary. The dictionary contains 1,10,000 words and meanings. I developed a vocabulary game where I had to randomly choose 10 words out of 1,10,000 dataset. Here I’m describing the possible solutions for retrieving 10 random words from 1,10,000…  Read Full Article

PlanetMySQL Voting: Vote UP / Vote DOWN

Some days ago I was working in a vocabulary game and dictionary. The dictionary contains 1,10,000 words and meanings. I developed a vocabulary game where I had to randomly choose 10 words out of 1,10,000 dataset. Here I’m describing the possible solutions for this problem and which solution I used.

Data table

Table name is dictionary and it has id, word and meaning fields. id contains auto incremented id and it is unbroken sequence 1,2,3…..n.

id	word	meaning
1	aback	Having the wind against the forward side of the sails
2	abandon	Forsake, leave behind
…	…..	….

Problem: Get 10 words out of 1,10,000 words.

Solution 1

I asked a intermediate level php programmer to solve this problem and I think he doesn’t know much about mysql. So he sent me the solution:

   //he retrieves all words from mysql to php
   $sql     =  "SELECT word FROM dictionary";
   $result =  mysql_query($sql);
   $rows  =  '';

   $data = array();
   if (!empty($result))
        $rows      =  mysql_num_rows($result);
   else
        $rows      =  '';

    $row = '';
    if (!empty($rows)){
        while ($row = mysql_fetch_assoc($result)){
                $data[]   = $row;
        }
    }

   //he shuffles the full array in php
   srand((float)microtime() * 1000000);
   shuffle($data);

   //then he collects 10 words
   $word = array();
   for ($i = 0; $i < 10; ++$i){
     $word[] = $data[$i];
   }

LOL , a brute force solution by that intermediate level programmer. This solution might be good for 1000 or not more than 2000 dataset. But never use the solution for small dataset also, if you solve a problem in mysql then solve that in there. Because it would be faster.

Solution 2

SELECT word FROM dictionary ORDER BY RAND() LIMIT 10;

A nice and simple mysql query. This simple query will give you 10 random words from the dictionary.  So you just have to run this query from your php code. Its one of the best solution for small size data like 25000 or not more than 40000. But remember your dataset is 1,10,000 words. So why not looking for another faster technique.

Solution 3

If you’re sure your dataset has unbroken sequence like 1,2,3…..n. You can solve this problem in another faster way.

   $randids = array();

   for($i = 1; $i <= 10; ++$i){
       $prev  = 1;
       $range = 10000 * $i;
       //mt_rand give random number from a given range
       $rndid  =  mt_rand($prev, $range);
       $randids[] = $rndid;
       $prev  = $range;
   }

   //we passed the ids we need from the table
    $sql     =  "SELECT word FROM dictionary WHERE id in (" . implode(",", $randids) . ")";
    $result =  mysql_query($sql);

Here you first randomly generated 10 random ids using php code. Then you pass those ids as string in mysql query. So mysql only returns those words from the table.

I used this solution for that vocabulary project I was working and if performs super. If you know better solution then why not you share that with us? Share your thoughts as comments.

PlanetMySQL Voting: Vote UP / Vote DOWN