Optimizing mysql random queries

I have two tables:

Table GAME: id(int), added_at(bigint)
Table META: id_game(int), meta(VARCHAR(64))

Now, each game can have 0 or more Meta tags related to it. I am trying to retrieve 9 games:

• 1 random game with "featured" META
• 1 random game with "premium" META
• 1 random game with "doublepoints" META
• 3 latest games (ORDER BY added_at DESC)
• 3 random games which are not any of the 6 games above

As of now I have a pretty cranky system of doing it, it looks more or less like this:

$feat = getGameMetaRandom(1, 'featured');
$prem = getGameMetaRandom(1, 'premium');
$dubl = getGameMetaRandom(1, 'doublepoints');
$last = getGameLatest(3);
$rand = getGameRandom(3);

At the moment each random function takes two queries (from getGameMetaRandom($count, $meta);):

SELECT FLOOR(RAND() * (COUNT(*) - " . ($count - 1) .")) AS `offset` 
FROM table_meta WHERE meta = '{$meta}'

SELECT t1.* FROM table_meta t2
LEFT JOIN table_game t1 ON t1.id = t2.id_game
WHERE t2.meta = '{$meta}' LIMIT {$offset}, {$count}

(gameRandom is very similar) As you can see this ignores my restriction of which are not any of the 6 games above, plus all of this takes 9 queries and the randomization is not truly random.

So my three objectives and my possible solutions are:

1. How to make 3 random games not repeat any of the aforeselected games. After selecting the first six games I cou开发者_JAVA百科ld probably list their IDs and use them in the last query with NOT IN () but that wouldn't be overly optimized.
2. How to make the random take games randomly, not selecting random offeset and taking n games from it? Using ORDER BY RAND() obviously, but I heard really bad things about how slow it is, though I suppose unless my table has hundreds of rows it doesn't make a difference?
3. How to reduce the number of queries? Group the first three queries into one, I am left with 5 queries, or by using the ORDER BY RAND() I can ignore the first "offset retrieving" query and go with something like SELECT t1.* FROM table_meta t2 LEFT JOIN table_game t1 ON t1.id = t2.id_game WHERE t2.meta = '{$meta}' ORDER BY RAND() LIMIT {$count}

But still, thiese generally require using ORDER BY RAND() and some test I saw made it look to be awfully slow. Any hints to improve it even more?

---

A games table:

root@localhost [kris]> show create table games\G
*************************** 1. row ***************************
       Table: games
Create Table: CREATE TABLE `games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `flags` enum('features','premium','doublepoints') NOT NULL,
  `added_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8184 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

A sample game:

root@localhost [kris]> insert into games values ( NULL, floor(rand() * 4 ), now() - interval 1200 second);
Query OK, 1 row affected, 1 warning (0.00 sec)

Note (Code 1592): Statement may not be safe to log in statement format.

More sample games:

root@localhost [kris]> insert into games select NULL, floor(rand() * 4), now() - interval 1200 second from games;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Note (Code 1592): Statement may not be safe to log in statement format.

Repeat above statement, until sufficient sample data. Data truncated warnings can be ignored, they are an artifact of the insertion of 0 into the enum() column, resulting in a flagless game, which is what we want.

root@localhost [kris]> select count(*) from games;
+----------+
| count(*) |
+----------+
|     8192 |
+----------+
1 row in set (0.00 sec)

We create a shuffled list of games:

root@localhost [kris]> create table shuffle like games;
Query OK, 0 rows affected (0.09 sec)

root@localhost [kris]> alter table shuffle modify column id integer not null, drop primary key, add column shuffleid integer not null auto_increment, add primary key (shuffleid), add index(flags), add index(added_at), add index(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

Shuffle the games:

root@localhost [kris]> insert into shuffle select id, flags, added_at, NULL from games order by rand();
Query OK, 8192 rows affected, 1 warning (0.34 sec)
Records: 8192  Duplicates: 0  Warnings: 0

Note (Code 1592): Statement may not be safe to log in statement format.

Now simply fetch what you need:

root@localhost [kris]> select min(id) as id from shuffle where flags = 'premium' 
    union all select min(id) from shuffle where flags = 'features' 
    union all select min(id) from games where flags = 'doublepoints' 
    union all ( select id from shuffle order by added_at limit 3 );
+------+
| id   |
+------+
| 8216 |
| 8214 |
| 8218 |
| 8213 |
| 8214 |
| 8216 |
+------+
6 rows in set (0.00 sec)

It is more efficient to select 3 random rows that are not in the above set in a second query:

root@localhost [kris]> select id from shuffle where id not in ( 8216, 8214, 8218, 8213, 8214, 8216) limit 3;
+------+
| id   |
+------+
| 8215 |
| 8219 |
| 8220 |
+------+
3 rows in set (0.00 sec)

Then delete the 9 values from the shuffle, so that a subsequent use of the table will generate 9 new values (or leave the 3 most recent thingies in, if you like).