3

I have a table named it_vit_qs from which i have to make a question set. Each question set should obtain qs from every category (e.g. cat_id) and every level (e.g lavel_id) in random order.

Note that each category(cat_id) have 3 level. There are 7 category. That means there will be 3*7= 21 combination. That means cat_id 1 have level_id 1,2 and 3, cat_id 2 have level_id 1,2 and 3 and so on.

Now how i can select 5 question from each cat & level combination in random order??? i have tried the below query but it seems like slow and bad practice. I have included my sql and its result. But I want to do it in efficient & easier way.

SELECT 
  a.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
  WHERE cat_id = 1 AND level_id=1
  ORDER BY RAND() 
  LIMIT 3) a 
UNION
SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 1 AND level_id=2
  ORDER BY RAND() 
  LIMIT 3) b 

  UNION

  SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 1 AND level_id=3
  ORDER BY RAND() 
  LIMIT 3) b

  UNION 

  SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 2 AND level_id=1
  ORDER BY RAND() 
  LIMIT 3) b

  UNION 

  SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 2 AND level_id=2
  ORDER BY RAND() 
  LIMIT 3) b

The table image

Updated:

I think it can also be done in php after running the query.

SELECT * FROM it_vit_qs ORDER BY RAND();

Can you please help me to do it in php from mysql query result.

Md. Shamvil Hossain
  • 340
  • 6
  • 24
  • Have you seen [this](https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) yet? The accepted answer looks good to me and the link in the answer does too. – Jeff Holt Jul 08 '17 at 06:36
  • @jeff6times7 No, that link doesn't look good to me it doesn't discuss taking a random subsample from a number of _groups_. – Tim Biegeleisen Jul 08 '17 at 06:37
  • the main problem is to meet the combination criteria. I have to select from each cat_id and its corresponding level_id. – Md. Shamvil Hossain Jul 08 '17 at 06:38
  • Showing us cleaner data would have been very helpful in giving you an answer. – Tim Biegeleisen Jul 08 '17 at 07:21

1 Answers1

1

You might consider populating a hashmap with rows fetched from this query:

SELECT * FROM it_vit_qs ORDER BY RAND()

As you fetch a row, you add a tuple as the value of a hashmap whose key is a combination of cat_id and level_id (e.g., (cat_id*10) + level_id). When the number of tuples for a combination reaches 5 you mark that key as being complete by adding the key to another hashmap, called C.

When the number of entries in C reaches 7, you stop fetching rows.

The reason your code takes so long is because your query makes 21 full passes on it_vit_qs.

The worst case for my approach is you make one full pass on it_vit_qs. And that remains true if you add more categories or levels.

Addendum 1 php example

<?php

$db = new mysqli(localhost, DB_USER, DB_PASSWORD, DB_NAME);
$stmt = $db->prepare("SELECT cat_id, level_id, other_id FROM it_vit_qs ORDER BY RAND()");
$stmt->execute();
$stmt->bind_result($cat_id, $level_id, $other_id);

$found = Array();
$complete = Array();

while ($stmt->fetch()) {
    $key = "$cat_id:$level_id";

    #if (count($found[$key]) < 5) {     # this might suffice

    if (!array_key_exists($key, $found) || count($found[$key]) < 5) {
       # if it_vit_qs has more than 3 columns, then push a list instead of a scalar
       if (count($found[$key]) == 0) {
          $found[$key] = Array();
       }
       array_push($found[$key], $other_id);
    } else {
       $complete[$key] = 1;
       if (count($complete) == 7) {
          break;
       }
   }
}
print_r($found);
?>

Addendum 2

<?php

$db = new mysqli(localhost, DB_USER, DB_PASSWORD, DB_NAME);
$stmt = $db->prepare("SELECT cat_id, level_id, other_id FROM it_vit_qs ORDER BY RAND()");
$stmt->execute();
$stmt->bind_result($cat_id, $level_id, $other_id);

$found = Array();
$complete = Array();

while ($stmt->fetch()) {
   $key = "$cat_id:$level_id";
   if (!array_key_exists($key, $found)) {
      $found[$key] = Array();
   }
   if (count($found[$key]) < 5) {
      array_push($found[$key], $other_id);
   } else {
      $complete[$key] = 1;
      if (count($complete) == 7) {
         break;
      }
   }
}
print_r($found);
?>
Jeff Holt
  • 2,940
  • 3
  • 22
  • 29