Thanks for your reply.
I select the ip and messages columns so that I could know the Visitors who and where during the time.
if you could provide any suggestion I will be appreciated.
I get 8GB RAM in my server and about another statement I am sorry about pasted the wrong result. Here is the correct:
mysql> explain SELECT L.LoginName, U.LoginName AS MainLoginName, U.LevelID,L.Message, L.LoginTime ,L.LoginIP FROM LRecord AS L JOIN User AS U ON( L.UserID = U.UserID ) WHERE (L.LoginTime >= '2011-10-11 00:00:00' AND L.LoginTime <= '2011-11-11 23:59:59') AND U.RoomID = '163'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: U
type: ref
possible_keys: PRIMARY,RoomID
key: RoomID
key_len: 4
ref: const
rows: 15552
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: L
type: ref
possible_keys: UserID,LoginTime
key: UserID
key_len: 4
ref: MainDB.U.UserID
rows: 31
Extra: Using where
2 rows in set (0.00 sec)
mysql> explain SELECT L.LoginName, U.LoginName AS MainLoginName,U.LevelID,L.Message, max(L.LoginTime) AS LoginTime , L.LoginIP FROM LRecord AS L JOIN User AS U ON( L.UserID = U.UserID ) WHERE L.LoginTime >= '2011-08-04 00:00:00' AND L.LoginTime <= '2011-11-12 23:59:59' AND U.RoomID = '163' AND U.LevelID>4 AND L.LoginIP IN (2018872567,2018872567,1948007961,3549143976,3549143976,1948007961,977185930,977185930,977185930,977185930,977185930) GROUP BY L.LoginName, L.LoginIP ORDER BY LoginIP ASC, LoginTime ASC \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: L
type: range
possible_keys: UserID,LoginIP,LoginTime
key: LoginIP
key_len: 4
ref: NULL
rows: 14924
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: U
type: eq_ref
possible_keys: PRIMARY,LevelID,RoomID
key: PRIMARY
key_len: 4
ref: MainDB.L.UserID
rows: 1
Extra: Using where
2 rows in set (0.08 sec)
mysql> show table status like 'User' \G;
*************************** 1. row ***************************
Name: User
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1349380
Avg_row_length: 45
Data_length: 60927580
Max_data_length: 281474976710655
Index_length: 72186880
Data_free: 0
Auto_increment: NULL
Create_time: 2011-11-16 14:00:01
Update_time: 2011-11-16 14:04:23
Check_time: 2011-11-16 14:04:29
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show table status like 'LRecord' \G;
*************************** 1. row ***************************
Name: LRecord
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 13512051
Avg_row_length: 60
Data_length: 810830372
Max_data_length: 281474976710655
Index_length: 469092352
Data_free: 0
Auto_increment: NULL
Create_time: 2011-11-14 18:38:13
Update_time: 2011-11-14 18:38:59
Check_time: 2011-11-14 18:40:59
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 268435456 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 1073741824 |
| myisam_sort_buffer_size | 134217728 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097152 |
+-------------------------------+------------+