High scores w/ MySQL - is this good/bad approach?

[IronChefMorimoto]

I'm finishing up a Flash quiz that's powered by PHP/MySQL, and I'm trying to figure out the best method for storing the high scores in the database. Here's an overview of what I had in mind:

• User finishes quiz and has a total points score stored
• User fills in 3-letter initials (arcade style) and submits their score
• Score goes into quizScores table (id, points_regular, points_bonus, points_total, initials, user_ip, user_browser, user_timestamp)
• After updating quizScores, check user score against the lowest score in quiz_HighScores
• Delete the lowest score in quiz_HighScores table and insert the user's high score IF the user's score is higher, then return the top 10 or top 20 high scores to the Flash interface
• If user's score is NOT higher, run SELECT on the anonymous quiz scores table and at least return the user's score rank from that table

Is there a better way to do this?

Keep in mind that I am not storing unique usernames as part of a registration or login system. This is strictly anonymous high scores. I'm storing ALL the scores in case I need to do a running tally of all scores. However, that's going to be slower than keeping a separate small batch of high scores in it's own table, right?

Finally, is it better to have the quiz_HighScores table structure the same as the quiz_Scores structure? Or should I use foreign keys tied to the quiz_Scores table?

If this is a bad approach, I'd like to know. Any suggestions are very appreciated.

IronChefMorimoto

 

[mikeblas]

The fourth step is "after upating quizScores". What is it that you update in quizScores, if the next thing to do is to delete then insert?

If you're storing ALL the scores, then why are you deleting anything?

Is the quizScores table the same as "the anonymous quiz scores table"?

What's the id column in quizScores for?

What is quiz_HighScores intended to store? The global highest scores, or the highest scores per user?

Why have two tables if you're storing all scores? This would only seem necessary if you have a really slow database, or millions of scores.

 

[IronChefMorimoto]

Good questions. I didn't explain myself very well, now that I think about it.

Here's what the quiz does:

• Admin user interface will allow creation of unlimited # of Flash-based quiz instances
• For each quiz instance, a unique ID -- the quiz configuration ID
• Need to associate, in a quizScores table, all anonymous user scores -- each score tied to a given quiz instance ID
• At the very least, the stored scores need to be called up to show either the top 10 or top 20 scores for a given quiz instance
• Would like to keep the score storage anonymous -- limited to the 3 arcade-like initials that we require the user to enter at the end of a given quiz instance; makes it hard to go back and do a SELECT on a specific score later, though

That said...I THOUGHT that making TWO tables would speed up finding the high scores:

• For every score recorded on a quiz instance, I would always INSERT each game's score in the quizScores table; it's gonna get big very fast if this tool is popular
• If the user's score was higher than the lowest score stored in the top 10/20 quizHighScores table, I would DELETE the last row and INSERT the new, higher score into quizHighScores; only 10-20 rows per quiz instance ever, possibly speeding up SELECT statements on the quizHighScores table (???)

I really do NOT want to store unique user information with this quiz -- our users already have a subscription login to go through for the site involved that I cannot tie into. I don't have API access and wouldn't know what to do with it if I did.

Hope this explains it better. Thanks for chiming in. Any suggestions are welcome and appreciated.

IronChefMorimoto

 

[mikeblas]

Sorry, but I'm still confused. Does the Quiz ID identify the instance of someone taking the quiz, or the configuration of the quiz? If the latter, it doesn't seem unique. It has to be the latter, doesn't it? Otherwise, how do you know who took which quiz?

I'd create this table:

(id, points_regular, points_bonus, points_total, initials, user_ip, user_browser, user_timestamp)

then, at the end of each quiz, INSERT the new score, then delete the lowest scores in a transaction.

This doesn't store all the scores. If I wanted to store all the scores, I'd create a table that stores all the scores, then create a view which had the top n scores grouped by each quiz ID. If performance required it, I'd create a materialized view -- though I guess MySQL doesn't have that feature. Without it, you'd have to also do the above INSERT/DELETE transaction on a seperate physical table.

 

[generelz]

I would not recommend using a separate table. You will end up storing redundant information. Most databases provide a "top N" function, so to fetch the 20 highest scores for a given quiz you would just do something like:

SELECT * FROM quiz_scores WHERE quizId = 'someQuizId' ORDER BY totalScore DESC LIMIT 20

I think you would find with an index on the quizId column and the totalScore column performance should not be an issue.

 

[IronChefMorimoto]

Sorry, but I'm still confused. Does the Quiz ID identify the instance of someone taking the quiz, or the configuration of the quiz? If the latter, it doesn't seem unique. It has to be the latter, doesn't it? Otherwise, how do you know who took which quiz?

Each quiz has a unique ID in a quizConfig table. This table stores the basics of the quiz - title, credits for those who researched it, etc. Each time I store a score in the quizScores table, I tie the score to a unique quiz ID from the quizConfig table -- the quiz ID can appear 100s of times through 100s of people playing it.

What I'm NOT storing is a unique username or user id for each quiz player. This is supposed to be arcade-ish. If I play Pac-Man, I get to enter my initials (e.g. BAB), but someone else could come up and enter a different score with the same initials. So the players are non-unique and will remain so simply because we don't want them having to log in to play.

I'd create this table:

(id, points_regular, points_bonus, points_total, initials, user_ip, user_browser, user_timestamp)

If I wanted to store all the scores, I'd create a table that stores all the scores, then create a view which had the top n scores grouped by each quiz ID. If performance required it, I'd create a materialized view -- though I guess MySQL doesn't have that feature. Without it, you'd have to also do the above INSERT/DELETE transaction on a seperate physical table.

This is kind of the route I was trying to take with storing the top 10 scores for each quiz configuration ID in a quizHighScores table.

As for transactions -- I'm lost on that. I'm a newb to PHP and MySQL -- I've been into ActionScript for way too long and am probably in over my head with this project.

Thanks,
IronChefMorimoto

 

[IronChefMorimoto]

I would not recommend using a separate table. You will end up storing redundant information. Most databases provide a "top N" function, so to fetch the 20 highest scores for a given quiz you would just do something like:

SELECT * FROM quiz_scores WHERE quizId = 'someQuizId' ORDER BY totalScore DESC LIMIT 20

I think you would find with an index on the quizId column and the totalScore column performance should not be an issue.

Thanks -- that's where I'm sort of at now. Here's what I'd LIKE to do AFTER I store the anonymous user's score in the quizScores table:

• If user's score is in the top 10 or 20, capture that fact and notify them (pretty easy)
• If user's score is out of the top 10 or 20, find, out of all scores for a given quiz ID, find the descending order position of their score and notify them of that (33rd out of 155 scores -- not as easy -- involves...performance robbing subquery, maybe?)

Thanks guys -- I appreciate the assistance.

IronChefMorimoto

 

[generelz]

• If user's score is out of the top 10 or 20, find, out of all scores for a given quiz ID, find the descending order position of their score and notify them of that (33rd out of 155 scores -- not as easy -- involves...performance robbing subquery, maybe?)

Unfortunately MySQL doesn't provide a ROW_NUMBER() function so yes you have to use a subquery with user-defined query variables to accomplish this. Or you could define a stored procedure or a view which could give you the same functionality.

 

[mikeblas]

MySQL has a non-standard LIMIT keyword, though; and I think it will cover it.

 

[Wiseguy2001]

Unfortunately MySQL doesn't provide a ROW_NUMBER() function so yes you have to use a subquery with user-defined query variables to accomplish this. Or you could define a stored procedure or a view which could give you the same functionality.

SELECT count(*) FROM quizScores WHERE points_total > {their score};

Should accomplish finding out what rank their score is.