Improving Query performance in mysql

Question

My Db table is as follows

|id|code|qn1|qn2|qn3|qn4|qn5|.............|qn38|qn39|qn40|name|modfiedDate|status|RollNo|

i am trying to optimize the query to fetch details from this table

i need to find the number of unanswerd question and option selected for the question i view currently

ex : qn1 hold value selected for question 1

Query

select * from tbl_name where code ='xx' and RollNo ='123';

view page display's one question at a time and i would need the total unanswered questions and has lots of hits

there are totally 40 fields for question sometime there may be only 22 question

so Does this query improve performance

$field_str="";
for($no=1;$no<=$TotalQ_count;$no++)
{   
    $field_str .= ",qn".$no;
}

$field_str =ltrim($field_str,",");

$querycnt = "SELECT status, $field_str from tbl_name
 where code ='xx' and RollNo ='123' ";

is there any other way to improve this query?

Bogdan Solomykin · Accepted Answer · 2013-10-08 07:41:12Z

1

You should create another database structure. Something like that:

table "tests"

id|date|some_other_columns...

and table "questions"

id|test_id|answer|date|some_other_columns

Then you can easily make queries:

SELECT
    COUNT(*) AS `unanswered_questions`
FROM `tests`
INNER JOIN `questions`
    ON `tests`.`id` = `questions`.`test_id`
WHERE 1
      AND `tests`.`id` = 5
      AND `questions`.`answer` IS NOT NULL

answered Oct 8, 2013 at 7:41

Bogdan Solomykin

1765 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Naveen Kumar Over a year ago

One question , will changing the query from select * from tbl_name to select almost_all_fields from tbl_name reduce the load on the server ?

Bogdan Solomykin Over a year ago

Of course, you should fetch only those columns that you need. This will help to improve the performance of queries.

Naveen Kumar Over a year ago

I am thinking about the overhead , does MYSQL fetch all the rows, then strip and return only required fields, or it fetches the required fields only

Collectives™ on Stack Overflow

Improving Query performance in mysql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related