Katen The manual has a section on optimization. If you have done deletes on this table, the table may not be optimized.
Try: OPTIMIZE TABLE RADPOOL1 It does lock the table while doing it. Other than that, I don't know what else to do. David -----Original Message----- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 8:47 AM To: Lopez David E-r9374c; [EMAIL PROTECTED] Subject: RE: select query optimization i made the change, but it looks like it didn't speed the query up at all. here are the results from the first explain: mysql> explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +---------+------+-----------------------+------------+---------+-------+------+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+-----------------------+------------+---------+-------+------+----------------------------+ | RADPOOL | ref | RADPOOL_I2,RADPOOL_I3 | RADPOOL_I2 | 20 | const | 6893 | where used; Using filesort | +---------+------+-----------------------+------------+---------+-------+------+----------------------------+ 1 row in set (0.00 sec) i made the changes to acoomodate this create statement: CREATE TABLE `RADPOOL1` ( `id` int(11) NOT NULL auto_increment, `STATE` tinyint(4) default NULL, `TIME_STAMP` int(11) NOT NULL default '0', `EXPIRY` int(11) default NULL, `USERNAME` char(35) default NULL, `POOL` char(20) NOT NULL default '', `YIADDR` char(15) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `RADPOOL_I` (`YIADDR`), INDEX ipoolstate( POOL, STATE ) ); 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) katen 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? > >-----Original Message----- >From: Steve Katen [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, April 10, 2002 8:07 AM >To: [EMAIL PROTECTED] >Subject: select query optimization > > >i have been working on getting this query as fast as possible and figured >it was time to come to the mailing list. > >the below table currently holds about 43 thousand records with potential to >grow to around 1,000,000 records. > >CREATE TABLE `RADPOOL` ( > `id` int(11) NOT NULL auto_increment, > `STATE` tinyint(4) default NULL, > `TIME_STAMP` int(11) NOT NULL default '0', > `EXPIRY` int(11) default NULL, > `USERNAME` char(35) default NULL, > `POOL` char(20) NOT NULL default '', > `YIADDR` char(15) NOT NULL default '', > PRIMARY KEY (`id`), > UNIQUE KEY `RADPOOL_I` (`YIADDR`), > KEY `RADPOOL_I2` (`POOL`), > KEY `RADPOOL_I3` (`STATE`), > KEY `RADPOOL_I4` (`TIME_STAMP`) >); > >the query below becomes slow when I added the ORDER BY clause to it. > >select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and >POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; > >the output received from the mysql client is "1 row in set (0.09 sec)" > >is there a way to speed this query up when using the ORDER BY? > >thanks in advance, > >katen > > > > > >--------------------------------------------------------------------- >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 --------------------------------------------------------------------- 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
