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:
mysql> SHOW 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_STAMP, A2.TIME_STAMP) AS RECTIME,
A1.USERNAME, FROM_UNIXTIME(A1.TIME_STAMP) AS ASTART,
FROM_UNIXTIME(A2.TIME_STAMP) AS ASTOP, A1.NASIDENTIFIER, A1.FRAMEDIPADDRESS,
A1.ACCTSESSIONID, A1.CALLINGSTATIONID, A2.ACCTTERMINATECAUSE,
A3.ACCTSESSIONTIME,
GREATEST(A3.ACCTINPUTOCTETS, A2.ACCTINPUTOCTETS, 0) AS ACCTINPUTOCTETS,
GREATEST(A3.ACCTOUTPUTOCTETS, A2.ACCTOUTPUTOCTETS, 0) 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.
dBforums
>
Help optimizing query...
01-20-05, 17:46
Linear Mode
