Question regarding indexing and table structure practices

[Craig Stadler]

Im at my wit's end!

Im using mysql 4.0.23 (win32)

Show create:
CREATE TABLE `dbcurrent` (
`Filespec` varchar(254) NOT NULL default '',
`ID` int(11) unsigned NOT NULL auto_increment,
`InUse` int(11) default NULL,
PRIMARY KEY (`Filespec`),
UNIQUE KEY `IDX_ID` (`ID`),
UNIQUE KEY `IDX_INUSE` (`InUse`,`Filespec`,`ID`)
) TYPE=MyISAM MAX_ROWS=100000000;

Query :
Select Filespec from dbCurrent where inUse is null order by id desc limit 1

Database size : 6,406,347

Is there a better way to index this table so the Query doesnt take forever
(this query takes about 30-45 seconds to execute) or a better way to word my
query???
Essentially I want the newest record in dbCurrent (by natural ID) thats not
marked InUse=1....

[Bill Karwin]

Craig Stadler wrote:

Query :
Select Filespec from dbCurrent where inUse is null order by id desc limit 1

Given MySQL 4.0.x, I would do this in two queries:

SELECT MIN(id) FROM dbCurrent WHERE inUse IS NULL

Save the result, and use it as a parameter to the second query:

SELECT Filespec FROM dbCurrent WHERE id = ?

Regards,
Bill K.