Two queries vs select all

Question

I am using Mysql and php.

This is my table structure:=

+----+---------------+--------+--------+--------+--------+--------+--------+
| id | no_of_replies | reply1 | reply2 | reply3 | reply4 | reply5 | reply6 |
+----+---------------+--------+--------+--------+--------+--------+--------+
| -- | --            |        | --     | --     | --     | --     | --     |
+----+---------------+--------+--------+--------+--------+--------+--------+

There are max six replies a user can give for a question.Most of the time user is giving 4-5 replies.i store the number of replies(no_of_replies) and all the replies(reply1,reply2,...). Mostly the reply5 and reply6 are NULL.

so when displaying data I have 2 options.

option1:- do single query and get all responses-

SELECT * FROM `responses` WHERE `id`='xyz'

option2:- do 2 queries and get required responses.

SELECT `no_of_repies` FROM `responses` WHERE `id`='xyz'

if result is 3

SELECT reply1,reply2,reply3 FROM responses WHERE id='XYZ'

NOTE:= I am asking this because this table is going to be used very often and needs to quickly give responses.

Why would you like to have a query that will return an unknown number of columns ? How will you programmatically process the result? The overhead of getting the empty/null results are negligable — nl-x, May 27 '14 at 09:47
Using RDMS would ease up your job. Instead of having `reply1, reply2...`, put those in a separate table. Then you won't have to worry about `no_of_replies`. Simply `SELECT reply_text FROM table2 WHERE foreign_key = primary_key` — asprin, May 27 '14 at 09:47
Is `id` the userid or questionid - and how do you know the other one? — kero, May 27 '14 at 09:48
@asprin I am not able to figure out how to put these columns in seperate table and how will it solve the problem — sid, May 27 '14 at 09:49
@sid And how do you know which question these replies belong to? — kero, May 27 '14 at 09:50
@kingkero have a separate table for questions having this id as foreign key between these tables — sid, May 27 '14 at 09:53

Poorya Mohammadi · Answer 1 · 2014-05-27T14:05:02.423

2

You should read about database normalization.In your case i would do it like this :

tabel
---------------------------------
id    reply     date   questinID

to get the amount of replies for 1 person for 1 question

SELECT COUNT(*) FROM tabel WHERE id=userid AND questionID=questionid;

to get the replies for 1 question for 1 user:

SELECT * FROM tabel WHERE id=userid AND questionID=questinid ORDER BY date;

edited May 27 '14 at 14:05

answered May 27 '14 at 10:00

Poorya Mohammadi

751
1
8
18

Two queries vs select all

1 Answers1