MySQL Forums
Forum List  »  Optimizer & Parser

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

(For reference)
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';

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;

> I select the ip and messages columns so that I could know the Visitors who and where during the time.
You will get some ip and some message. You won't get all of them.

> if you could provide any suggestion I will be appreciated.
I don't understand the goal of the query.

How many rows are returned by the queries? The first one may be hitting a million rows (between the two tables) to get the resultset. And it will have to generate a temp table of perhaps a million rows.

Options: ReplyQuote


Subject
Views
Written By
Posted
1686
November 14, 2011 01:10AM
795
November 14, 2011 09:24PM
955
November 16, 2011 01:20AM
Re: table join optimize help!
822
November 16, 2011 09:41PM
868
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.