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
