Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Help optimizing query...

    I'm trying to optimize a query that is majorly slowed down by my ORDER BY clause which unfortunately is sorting on the output of a GREATEST() call to a column and a LEFT JOIN'd column...

    The query...

    PHP Code:
     mysqlSHOW COLUMNS FROM ACCOUNTING200501;
    +--------------------+-------------+------+-----+---------+-------+
    Field              Type        Null Key | Default | Extra |
    +--------------------+-------------+------+-----+---------+-------+
    USERNAME           varchar(50) |      | MUL |         |       |
    TIME_STAMP         int(11)     | YES  MUL NULL    |       |
    ACCTSTATUSTYPE     varchar(10) | YES  |     | NULL    |       |
    ACCTDELAYTIME      int(11)     | YES  |     | NULL    |       |
    ACCTINPUTOCTETS    int(11)     | YES  |     | NULL    |       |
    ACCTOUTPUTOCTETS   int(11)     | YES  |     | NULL    |       |
    ACCTSESSIONID      varchar(30) | YES  MUL NULL    |       |
    ACCTSESSIONTIME    int(11)     | YES  |     | NULL    |       |
    ACCTTERMINATECAUSE varchar(32) | YES  |     | NULL    |       |
    NASIDENTIFIER      varchar(50) | YES  MUL NULL    |       |
    NASPORT            int(11)     | YES  |     | NULL    |       |
    FRAMEDIPADDRESS    varchar(22) | YES  MUL NULL    |       |
    CALLINGSTATIONID   varchar(15) | YES  MUL NULL    |       |
    +--------------------+-------------+------+-----+---------+-------+

         
    SELECT
            GREATEST
    (A1.TIME_STAMPA2.TIME_STAMP) AS RECTIME,
            
    A1.USERNAMEFROM_UNIXTIME(A1.TIME_STAMP) AS ASTART,
            
    FROM_UNIXTIME(A2.TIME_STAMP) AS ASTOPA1.NASIDENTIFIERA1.FRAMEDIPADDRESS,
            
    A1.ACCTSESSIONIDA1.CALLINGSTATIONIDA2.ACCTTERMINATECAUSE,
            
    A3.ACCTSESSIONTIME,
            
    GREATEST(A3.ACCTINPUTOCTETSA2.ACCTINPUTOCTETS0) AS ACCTINPUTOCTETS,
            
    GREATEST(A3.ACCTOUTPUTOCTETSA2.ACCTOUTPUTOCTETS0) AS ACCTOUTPUTOCTETS,
            
    SUBSTRING_INDEX(RADCLIENTLIST.HOSTNAME'.'1) AS HOSTNAME
          FROM ACCOUNTING200501 A1
          LEFT OUTER JOIN ACCOUNTING200501 A2 ON
            A1
    .ACCTSESSIONID A2.ACCTSESSIONID AND
            
    A2.ACCTSTATUSTYPE 'Stop'
          
    LEFT OUTER JOIN RADONLINE A3 ON
            A1
    .ACCTSESSIONID A3.ACCTSESSIONID
          LEFT OUTER JOIN RADCLIENTLIST ON
            RADCLIENTLIST
    .NASIDENTIFIER A1.NASIDENTIFIER
          WHERE
            1
          ORDER BY
            RECTIME DESC
          LIMIT 30 
    Obviously this isn't very fast as it has to create a temporary table before it can sort by `RECTIME`. Can anyone see a better way to do this? I essentially want to sort by the greatest of the two ASTOP and ASTART columns -- but I can only get these in the same row by joining them... any way to create an index on this sort of thing?

    Perhaps I should look into setting up another table to cache all this information so I'm not having to do joins like this...

    I am using MySQL 4.1.8 and have also tried using MySQL 5.0.2 in the hopes that a VIEW might speed things up (making another post on this).

    Thanks.

  2. #2
    Join Date
    Dec 2004
    Posts
    26
    It would help to see all tables involved (RADCLIENTLIST and RADONLINE), the indexes on all tables (SHOW INDEX FROM ...), and the current join plan (the output of EXPLAIN for that query).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •