Need to do this more efficiently...

  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

I wrote some code to show a site poll on my website and it works fine, but I feel it is very inefficient, can someone help me simplify this into 1 query or not have it use RAND()?

I have 3 tables, one for "Ballots" or groups of poll questions. Then the "Questions" that are in the ballots. Then the "Answers" for that belong to each question. The following code displays 1 random question from an open ballot, then it randomly orders all the child answers from the parent question.

PHP Code: [ Select ]
<?php
      $POLL_SQL = "SELECT ";
      $POLL_SQL .= "B.id, B.StartTime, B.EndTime, B.BallotName, B.BallotDesc, B.GameID, B.CompetitionID, ";
      $POLL_SQL .= "Q.id AS question_id, Q.parent_ballot_id, Q.question ";
      $POLL_SQL .= "FROM votingpolls_ballots B, votingpolls_questions Q ";
      $POLL_SQL .= "WHERE ";
      $POLL_SQL .= "(B.EndTime = 0 OR B.EndTime > ".time().") ";//Hasn't Ended
      $POLL_SQL .= "AND B.StartTime > 0 ";//Ballot Started
      $POLL_SQL .= "AND Q.parent_ballot_id = B.id ";
      $POLL_SQL .= "ORDER BY RAND() LIMIT 0,1";
 
      if($POLL_RESULT = $db->query($POLL_SQL))
      {
       while($POLL_DATA = $POLL_RESULT->fetch_array())
       {
       $template->assign_block_vars('side_table.site_poll',array(
       'QUESTION_ID' => intval($POLL_DATA['question_id']),
       'QUESTION' => DBStringExtract($POLL_DATA['question'])
       ));
         $ANSWERS_SQL = "SELECT ";
         $ANSWERS_SQL .= "id, parent_question_id, answer ";
         $ANSWERS_SQL .= "FROM votingpolls_answers ";
         $ANSWERS_SQL .= "WHERE parent_question_id = ".intval($POLL_DATA['question_id'])." ";
         $ANSWERS_SQL .= "ORDER BY RAND()";
 
         if($ANSWERS_RESULT = $db->query($ANSWERS_SQL))
         {
          while($ANSWERS_DATA = $ANSWERS_RESULT->fetch_array())
          {
            $template->assign_block_vars('side_table.site_poll.answer',array(
            'ANSWER_ID' => intval($ANSWERS_DATA['id']),
            'ANSWER' => DBStringExtract($ANSWERS_DATA['answer'])
            ));
          }
         }
 
       break;
       }
      }
?>
  1. <?php
  2.       $POLL_SQL = "SELECT ";
  3.       $POLL_SQL .= "B.id, B.StartTime, B.EndTime, B.BallotName, B.BallotDesc, B.GameID, B.CompetitionID, ";
  4.       $POLL_SQL .= "Q.id AS question_id, Q.parent_ballot_id, Q.question ";
  5.       $POLL_SQL .= "FROM votingpolls_ballots B, votingpolls_questions Q ";
  6.       $POLL_SQL .= "WHERE ";
  7.       $POLL_SQL .= "(B.EndTime = 0 OR B.EndTime > ".time().") ";//Hasn't Ended
  8.       $POLL_SQL .= "AND B.StartTime > 0 ";//Ballot Started
  9.       $POLL_SQL .= "AND Q.parent_ballot_id = B.id ";
  10.       $POLL_SQL .= "ORDER BY RAND() LIMIT 0,1";
  11.  
  12.       if($POLL_RESULT = $db->query($POLL_SQL))
  13.       {
  14.        while($POLL_DATA = $POLL_RESULT->fetch_array())
  15.        {
  16.        $template->assign_block_vars('side_table.site_poll',array(
  17.        'QUESTION_ID' => intval($POLL_DATA['question_id']),
  18.        'QUESTION' => DBStringExtract($POLL_DATA['question'])
  19.        ));
  20.          $ANSWERS_SQL = "SELECT ";
  21.          $ANSWERS_SQL .= "id, parent_question_id, answer ";
  22.          $ANSWERS_SQL .= "FROM votingpolls_answers ";
  23.          $ANSWERS_SQL .= "WHERE parent_question_id = ".intval($POLL_DATA['question_id'])." ";
  24.          $ANSWERS_SQL .= "ORDER BY RAND()";
  25.  
  26.          if($ANSWERS_RESULT = $db->query($ANSWERS_SQL))
  27.          {
  28.           while($ANSWERS_DATA = $ANSWERS_RESULT->fetch_array())
  29.           {
  30.             $template->assign_block_vars('side_table.site_poll.answer',array(
  31.             'ANSWER_ID' => intval($ANSWERS_DATA['id']),
  32.             'ANSWER' => DBStringExtract($ANSWERS_DATA['answer'])
  33.             ));
  34.           }
  35.          }
  36.  
  37.        break;
  38.        }
  39.       }
  40. ?>


I read on this guys blog about how you can use mt_rand() instead of RAND() [ http://akinas.com/pages/en/blog/mysql_random_row/ ], but I have 2 sets of things I need randomized and 2 queries, so I was wondering if that was even realistic, considering it would create 4 queries and he claims only 79% of RAND() execution time. My main problem is I've never used RAND() and I suck when it comes to reducing MySQL queries in loops.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

The first thing that comes to mind, is to use a query that will return everything you have it returning right now, and in addition the number of votes each item has.

Then you can order by votes ASC, limit to maybe 3-5, then pick a random item out of that small array using PHP. It should give you a better distribution of votes than ordering using RAND().

I'm somewhat preoccupied at the moment though, so I might be off.

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 117 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.