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
