Effecient mysql query multiple tables

Question

Hi all,

I have a database with 2000 games. Each game consists of 15 numbers from 1-90.

Each time I submit a number in the database, I want my results to update, so I’m always seeing the games that are nearest completion. (Only viewing 50 games at a time).

My database structure looks like this:

Table: game

• id
• name

Table: game_numbers

• game_id
• number

Table: numbers_played

• number

Example: Table game contains the games. The 15 numbers for each game, ranging 1-90 is stored in *game_numbers*, where *game_id* identifies the game. Table *numbers_played* contains the random numbers from 1-90 that has been played.

How do I pull the 50 games with the most numbers played (means results that exists in both game_numbers and numbers_played), keeping efficiency and simplicity in mind?

Thanks in advance!

Regards

Answer 1

untested...

  SELECT game_id, COUNT(*)
    FROM game_numbers
   WHERE number IN (SELECT number FROM numbers_played)
GROUP BY game_id
ORDER BY COUNT(*) DESC
   LIMIT 50;

Answer 2

Create a column which is the "numbers played" (i.e. a de-normalized field, i.e. duplicate of the information which could be collected from "numbers_played" table.

Then create an index on that column then if you "order by", MySQL can use the index to avoid needing to sort.

However, check out to see if you have a performance problem first. If you don't, then you'll save yourself effort and the system will be more normalized if you just leave it like it is, with a query over multiple tables and "order by" the aggregation.