Hi.
As I said, or at least, tried to say, a key over all used columns
should help. Adding the column from ORDER BY avoids the "using
filesort". Adding YIADDR (which I simply overlooked last time) should
add "using index", as I mentioned. This means, that the data file is
not touched at all, but only the index file. I.e.
INDEX ( POOL, STATE, TIME_STAMP, YIADDR )
Of course you have to pay off a bit with disk usage and insert speed.
But if select speed is that important, that's usually a good trade off.
Bye,
Benjamin.
On Wed, Apr 10, 2002 at 11:52:03AM -0700, [EMAIL PROTECTED] wrote:
> ben,
>
> I did that about three minutes after I got Davids email. Things seem to be
> working a lot faster now, and the "using filesort" is gone. i am assuming
> there isn't much more i can do to make this thing faster, but anything else
> i can do would be awesome!
>
> heres is the explain:
> mysql> explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and
> POOL='GLOBAL-POOL-SMF' order by TIME_STAMP limit 1;
>
>+---------+------+---------------+------------+---------+-------------+------+------------+
> | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
>
>+---------+------+---------------+------------+---------+-------------+------+------------+
> | RADPOOL | ref | ipoolstate | ipoolstate | 22 | const,const |
> 9416 | where used |
>
>+---------+------+---------------+------------+---------+-------------+------+------------+
> 1 row in set (0.00 sec)
>
> thanks for your response! =)
>
> katen
>
> At 08:48 PM 4/10/2002 +0200, Benjamin Pflugmann wrote:
[...]
> >The slow part is probably the "using filesort". I am not sure, if it
> >will work, but try a key over all used column, i.e.
> >
> >INDEX ( POOL, STATE, TIMESTAMP )
> >
> >If it works (i.e. if MySQL correctly "sees" that it can skip the
> >filesort), EXPLAIN should show something like "using index" instead of
> >"using filesort" and the query run a lot faster again.
[...]
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
