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: >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
