MySQL Forums
Forum List  »  Optimizer & Parser

Re: table join optimize help!
Posted by: kelvin chou
Date: November 16, 2011 01:20AM

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    |
+-------------------------------+------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
1630
November 14, 2011 01:10AM
770
November 14, 2011 09:24PM
Re: table join optimize help!
895
November 16, 2011 01:20AM
796
November 16, 2011 09:41PM
844
November 18, 2011 01:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.