mysql query optimization for game -

mysql query optimization for game -

i have table cardgame 2-4 players:

create table if not exists `cardgame` ( `id` mediumint(8) unsigned not null auto_increment, `players` tinyint(1) unsigned not null default '0', `p1` mediumint(6) unsigned not null default '0', `p2` mediumint(6) unsigned not null default '0', `p3` mediumint(6) unsigned not null default '0', `p4` mediumint(6) unsigned not null default '0', `p1_state` tinyint(1) unsigned not null default '0', `p2_state` tinyint(1) unsigned not null default '0', `p3_state` tinyint(1) unsigned not null default '0', `p4_state` tinyint(1) unsigned not null default '0', /* other rows */, `game_state` tinyint(1) unsigned not null default '0', primary key (`id`) ) engine=myisam auto_increment=79218 default charset=utf8;

i have check games, player. (my user_id 1981 in example)

mysql> explain select id cardgame ((p1=1981 , p1_state=1) or (p2=1981 , p2_state=1) or (p3=1981 , p3_state=1) or (p4=1981 , p4_state=1)) , game_state < 7; +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | 1 | simple | cardgame | | null | null | null | null | 79208 | using | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+

on rows can create indexes? p1, p1_state, p2.. p3.. p4, p4_state rows needs index plus game_state, isn't much?

or have redesign schema in way?

or have redesign schema in way?

yes, normalise schema:

create table game_players ( game_id mediumint not null, position tinyint not null, player mediumint not null, state tinyint unsigned not null default 0, primary key (game_id, position), unique (game_id, player), -- if player can in each game 1 time index (player, state) ) select id, 1, p1, p1_state cardgame union select id, 2, p2, p2_state cardgame union select id, 3, p3, p3_state cardgame union select id, 4, p4, p4_state cardgame; alter table cardgame drop p1, drop p1_state, drop p2, drop p2_state, drop p3, drop p3_state, drop p4, drop p4_state;

then query becomes:

select g.id game_players p bring together cardgame g on p.game_id = g.id p.player = 1981 , p.state = 1 , g.game_state < 7;

mysql query-optimization database-schema