MySQL Forums
Forum List  »  Optimizer & Parser

Re: table join optimize help!
Posted by: Rick James
Date: November 14, 2011 09:24PM

First, let's see if it is doing what you want.
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;
Note that it is GROUPing BY two fields, but there are 5 non-aggregate values in the SELECT. Which IP and Message do you expect to get? It may not be the one corresponding to the last LoginTime.

> `LoginIP` int(10) unsigned NOT NULL DEFAULT '0',
If that is an IP address, it cannot handle IPv6.

To assist in analyzing slow SELECTs, please provide
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

It could be as simple as having too small a key_buffer. Read
http://mysql.rjweb.org/doc.php/memory

Switching to InnoDB may help, but read
http://mysql.rjweb.org/doc.php/myisam2innodb

Perhaps you pasted things wrong? There is no way this would use the index on LoginIP:
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' 

Options: ReplyQuote


Subject
Views
Written By
Posted
1951
November 14, 2011 01:10AM
Re: table join optimize help!
934
November 14, 2011 09:24PM
1162
November 16, 2011 01:20AM
964
November 16, 2011 09:41PM
1027
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.