When multiple users send this query to server it slows down

[chrisalviola]

This is my SQL in PHP
SELECT * FROM ExamQuestions where exam_id not in(Select ans_exam_id from StudAns where ans_user_id=".$_COOKIE['cuserid']." and ans_exam_id in(SELECT exam_id FROM ExamQuestions)) ORDER BY RAND()

when multiple users send this query to server it slows down. is there any way i can simplify this? do i need to use join or create view to increase fetching speed?

 

[CompuChip]

Well, the "ans_exam_id in(SELECT exam_id FROM ExamQuestions)" part seems a bit redundant to me. I don't know your table structure, but does this actually add anything useful?

One thing you could do, since you are using PHP anyway, is simplify the query to simply give a list of ALL (possible) exam questions. Then use array_rand or shuffle to get some random elements.

 

[chrisalviola]

Well, the "ans_exam_id in(SELECT exam_id FROM ExamQuestions)" part seems a bit redundant to me. I don't know your table structure, but does this actually add anything useful?

One thing you could do, since you are using PHP anyway, is simplify the query to simply give a list of ALL (possible) exam questions. Then use array_rand or shuffle to get some random elements.

The goal of my query is to show only questions that are not yet answered and it should be in random order.

if you could suggest the best query that would reduce traffic i would be great full.

heres my table structure

    $query = "CREATE TABLE users (user_id INT (4) NOT NULL AUTO_INCREMENT, user_lastname TEXT,user_firstname TEXT, user_email TEXT, user_subcode TEXT, user_score INT (4), user_date DATETIME, user_ip TEXT, PRIMARY KEY (user_id))";

    $result = mysql_query($query) or die("Query failed");    

    print "Table users is created !<br>";



    $query = "CREATE TABLE admin (admin_username TEXT, admin_password TEXT, admin_ip TEXT, admin_date DATETIME)";

    $result = mysql_query($query) or die("Query failed");     

    print "Table admin is created !<br>";

    

    $query = "INSERT INTO admin (admin_username, admin_password) VALUES ('$ad_login', '$ad_passc')";

    $result = mysql_query($query) or die("Query failed");       

    print "Administrator account created !<br>";



    $query = "CREATE TABLE ExamQuestions (exam_id INT (4) NOT NULL AUTO_INCREMENT, exam_q LONGTEXT,opt1 TEXT, opt2 TEXT, opt3 TEXT, opt4 TEXT, correct_ans INT (4), PRIMARY KEY (exam_id))";

    $result = mysql_query($query) or die("Query failed");    

    print "Table ExamQuestions is created !<br>";



    $query = "CREATE TABLE StudAns (ans_id INT (4) NOT NULL AUTO_INCREMENT, ans_user_id INT (4), ans_exam_id INT (4), stud_ans INT (4), PRIMARY KEY (ans_id))";

    $result = mysql_query($query) or die("Query failed");    

    print "Table StudAns is created !<br></div>";