There was a problem and its solution will be interesting, I think, not only for me.
In what essence:
I create quiz the game...
On a condition, there is the base of questions, placed, for example, in table Quests.
Players in the game answer on 10 randomly (it important!) selected questions.
Results of the answer (correctly/wrong) are saved in table Answers.
Clearly...
Illustration of my tables:
Table Quests Table Answers
+----------------+ +-------+---------------------+
| Qnum | Qtext | | User | Qnum | AnswerStatus |
+------+---------+ +-------+------+--------------+
| 1 | bla bla | | User1 | 1 | 1 |
| 2 | ...... | | User1 | 10 | 0 |
| 3 | | | User1 | 5 | 0 |
| 4 | | | Blin | 4 | 0 |
| 5 | | | Buse | 4 | 1 |
| 6 | | | Alex | 1 | 1 |
| 7 | | | Alex | 2 | 1 |
| 8 | | | Alex | 4 | 1 |
| .... | | | .... | .... | ... |
+------+---------+ +--------------+--------------+
about 2 000 records 100 000+ records
Now just also start interesting!
When user starts new game, to the program are necessary to select for him new 10 questions,
from questions on which he yet has not answered, or has answered incorrectly.
In other words, to subtract from table Quests records with User = <CurrentUser> AND AnswerStatus=1
of table Answers.
Certainly, it is possible to solve this problem using JOIN thus:
SELECT Quests.Qnum, Quests.Qtext
FROM Quests LEFT JOIN Answers
ON ( Quests.Qnum=Answers.Qnum AND Answers.User=<CurrentUser> )
WHERE ( Answers.Status IS NULL Answers.Status<>1 )
ORDER BY RAND()
LIMIT 10
I.e. to already available columns in table Quests we add a column with the status of this question
(if its defined), in the total we use questions either with the uncertain status or with
incorrect status.
Purely the problem is, that at growing base and many users,
such query can be executed by mySQL about 1 minutes that is obviously unacceptable.
Who found solutions of a similar problem?
Would be very grateful, if you have told about methods of its solution or
modifying structure of a DB for speed up query speed.
Many thanks!
Andrey.
In what essence:
I create quiz the game...
On a condition, there is the base of questions, placed, for example, in table Quests.
Players in the game answer on 10 randomly (it important!) selected questions.
Results of the answer (correctly/wrong) are saved in table Answers.
Clearly...
Illustration of my tables:
Table Quests Table Answers
+----------------+ +-------+---------------------+
| Qnum | Qtext | | User | Qnum | AnswerStatus |
+------+---------+ +-------+------+--------------+
| 1 | bla bla | | User1 | 1 | 1 |
| 2 | ...... | | User1 | 10 | 0 |
| 3 | | | User1 | 5 | 0 |
| 4 | | | Blin | 4 | 0 |
| 5 | | | Buse | 4 | 1 |
| 6 | | | Alex | 1 | 1 |
| 7 | | | Alex | 2 | 1 |
| 8 | | | Alex | 4 | 1 |
| .... | | | .... | .... | ... |
+------+---------+ +--------------+--------------+
about 2 000 records 100 000+ records
Now just also start interesting!
When user starts new game, to the program are necessary to select for him new 10 questions,
from questions on which he yet has not answered, or has answered incorrectly.
In other words, to subtract from table Quests records with User = <CurrentUser> AND AnswerStatus=1
of table Answers.
Certainly, it is possible to solve this problem using JOIN thus:
SELECT Quests.Qnum, Quests.Qtext
FROM Quests LEFT JOIN Answers
ON ( Quests.Qnum=Answers.Qnum AND Answers.User=<CurrentUser> )
WHERE ( Answers.Status IS NULL Answers.Status<>1 )
ORDER BY RAND()
LIMIT 10
I.e. to already available columns in table Quests we add a column with the status of this question
(if its defined), in the total we use questions either with the uncertain status or with
incorrect status.
Purely the problem is, that at growing base and many users,
such query can be executed by mySQL about 1 minutes that is obviously unacceptable.
Who found solutions of a similar problem?
Would be very grateful, if you have told about methods of its solution or
modifying structure of a DB for speed up query speed.
Many thanks!
Andrey.