large table issue

[曹凯]

Hi all,

do you guys know how to deal with the large tables? 

here's my problem:

I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 
) and a server for load balancing.

What I'm maintaining is a game data tracking system. There's a game_log table 
which will record all detail info from many games.

here's the structure:

  `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  `session_id` varchar(128) NOT NULL COMMENT 'flash session id',
  `score` int(10) unsigned DEFAULT NULL,
  `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle  
2:been handle',
  `game_end` bigint(20) DEFAULT NULL,
  `game_start` bigint(20) unsigned NOT NULL DEFAULT '0',
  `event_time` float DEFAULT '0',
  PRIMARY KEY (`game_log_id`),
  KEY `game_id` (`game_id`),
  KEY `event_id` (`event_id`),
  KEY `player_id` (`player_id`)

it currently has about 1220 records( 2 or 3 of the other tables have around 
a million records for each ). now, it's very slow to query this table even I 
just query this single table. most of the time it failed.

do you guys know what the problem is?  or how to make it more efficient and 
faster?

thanks in advance

CK
  
_
一张照片的自白――Windows Live照片的可爱视频介绍
http://windowslivesky.spaces.live.com/blog/cns!5892B6048E2498BD!889.entry

RE: large table issue

[曹凯]

hi huys,

here's the explain of a query on this table

EXPLAIN SELECT COUNT(game_log_id) AS sum2 FROM game_log AS g, player AS p WHERE 
g.player_id = p.player_id AND  g.game_id=p.game_id=27 AND p.type=1 AND 
g.event_id = 32
- ;
++-+---+++--+-+++-+
| id | select_type | table | type   | possible_keys  | key  | key_len | 
ref| rows   | Extra   |
++-+---+++--+-+++-+
|  1 | SIMPLE  | g | ref| event_id,player_id | event_id | 4   | 
const  | 237894 | |
|  1 | SIMPLE  | p | eq_ref | PRIMARY| PRIMARY  | 4   | 
trigger_replay.g.player_id |  1 | Using where |
++-+---+++--+-+++-+
2 rows in set (0.00 sec)

and,  index on 'game_id', 'event_id', 'player_id'

CK
  
_
想知道明天天气如何？必应告诉你！
http://cn.bing.com/search?q=%E5%A4%A9%E6%B0%94%E9%A2%84%E6%8A%A5form=MICHJ2

how to get the name of primary key ?

[曹凯]

Hi all,

if we just know the table name but don't know the name of primary key, is there 
any variables or constants could instead of the PK?

for example:

there is a table game_log, and now I have the last inserted_id but don't know 
what its primary_id is, 
how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id?


  
_
约会说不清地方？来试试微软地图最新msn互动功能！
http://ditu.live.com/?form=TLswm=1

RE: how to get the name of primary key ?

[曹凯]

Hi Thiyaghu,

I have already got the last_insert_id. now I wanna know if there are any 
variables or constants to instead of game_log's primary key cos I don't know 
its name.






 Date: Mon, 1 Feb 2010 14:24:59 +0530
 Subject: Re: how to get the name of primary key ?
 From: theyaho...@gmail.com
 To: tx...@hotmail.com
 CC: mysql@lists.mysql.com
 
 Hi,
 
 Use
 
 *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()*
 
 It will help you.
 
 Regards,
 Thiyaghu CK,
 MySQL DBA
 www.mafiree.com
 
 2010/2/1 曹凯 tx...@hotmail.com
 
 
  Hi all,
 
  if we just know the table name but don't know the name of primary key, is
  there any variables or constants could instead of the PK?
 
  for example:
 
  there is a table game_log, and now I have the last inserted_id but don't
  know what its primary_id is,
  how can I SELECT * FROM game_log WHERE this_table's_PK =
  last_inserted_id?
 
 
 
  _
  约会说不清地方？来试试微软地图最新msn互动功能！
  http://ditu.live.com/?form=TLswm=1
  
_
约会说不清地方？来试试微软地图最新msn互动功能！
http://ditu.live.com/?form=TLswm=1

RE: how to get the name of primary key ?

[曹凯]

hi Jesper,


thanks a lot!   That's what I want. thank u againCC: mysql@lists.mysql.com
From: jes...@noggin.com.au
Subject: Re: how to get the name of primary key ?
Date: Mon, 1 Feb 2010 20:26:36 +1100
To: tx...@hotmail.com

On 01/02/2010, at 7:33 PM, 曹凯 wrote:
Hi all,
if we just know the table name but don't know the name of primary key, is there 
any variables or constants could instead of the PK?
for example:
there is a table game_log, and now I have the last inserted_id but don't know 
what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK 
= last_inserted_id?
You can get the column name from the information schema, however that can't be 
used directly in another query in the way you've done in your example. E.g.
game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE 
TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 
'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID   | 
+-+1 row in set (0.00 sec)
where it is assumed the database name is game.
Hope that helps.
Jesper
_
Windows Live社区两周年，拿奖过新年！
http://events.livetome.cn/2010/2birthday