Hi.
(I am replying to the wrong mail, because I already delete the one
from Steve...)
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.
Bye,
Benjamin.
PS: And to answer the other question, yes INDEX and KEY are synonyms
in MySQL (see http://www.mysql.com/doc/C/R/CREATE_TABLE.html).
[...]
> -----Original Message-----
> From: Steve Katen [mailto:[EMAIL PROTECTED]]
[...]
> i made the change, but it looks like it didn't speed the query up at all.
[...]
> here are the new explain results:
> mysql> explain select TIME_STAMP, YIADDR from RADPOOL1 where STATE=0 and
> POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1;
>
>+----------+------+---------------+------------+---------+-------------+------+----------------------------+
> | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
>
>+----------+------+---------------+------------+---------+-------------+------+----------------------------+
> | RADPOOL1 | ref | ipoolstate | ipoolstate | 22 | const,const |
> 6011 | where used; Using filesort |
>
>+----------+------+---------------+------------+---------+-------------+------+----------------------------+
> 1 row in set (0.00 sec)
>
> At 08:35 AM 4/10/2002 -0700, Lopez David E-r9374c wrote:
> >Katen
> >
> >Try using a compound index with STATE and POOL
> >
> > INDEX ipoolstate( POOL, STATE )
> >
> >Use EXPLAIN SELECT .... to see what mysql thinks.
> >
> >David
> >
> >PS anybody know if KEY is the same as INDEX?
[...]
--
[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
