"ORDER BY" without large table scans and filesort

[Andreas]

OK, this seems to be impossible but I thought I could ask anyway, maybe I overlooked smth. and somebody has the 1.000.000 $ answer

Let's say I got a table with several tenthousand records; the primary index as an auto-increment integer.
Now I want the last record.
I could do
[sql]SELECT * FROM table ORDER BY id DESC LIMIT 1[/sql]

But this causes large table scans ...

[Marco van Herwaarden]

Primary index should be an unique index, and not cause full table scans.

Try playing with a HAVING max(id).

[Andreas]

Hmm ...

[sql]EXPLAIN SELECT * FROM thread ORDER BY threadid DESC LIMIT 1[/sql]

 
id  	select_type	table	type	possible_keys	key	key_len		ref	rows  	 Extra
1 	SIMPLE 		thread	ALL	NULL		NULL 	NULL		NULL	32

I only got 32 Threads on my testboard, so this is a full tablescan.
Any ideas?

[Marco van Herwaarden]

Well this seems like there is no index on threadid. Try creating another unique ascending index on threadid.

[Andreas]

Erm ... threadid is the PRIMARY index of table thread
Although it IMHO doesn't make sense to add another index to the column, i did so - no change.

Schema
[sql]
CREATE TABLE thread (
threadid int(10) unsigned NOT NULL auto_increment,
title varchar(250) NOT NULL default '',
firstpostid int(10) unsigned NOT NULL default '0',
lastpost int(10) unsigned NOT NULL default '0',
forumid smallint(5) unsigned NOT NULL default '0',
pollid int(10) unsigned NOT NULL default '0',
`open` smallint(6) NOT NULL default '0',
replycount int(10) unsigned NOT NULL default '0',
postusername varchar(100) NOT NULL default '',
postuserid int(10) unsigned NOT NULL default '0',
lastposter varchar(50) NOT NULL default '',
dateline int(10) unsigned NOT NULL default '0',
views int(10) unsigned NOT NULL default '0',
iconid smallint(5) unsigned NOT NULL default '0',
notes varchar(250) NOT NULL default '',
visible smallint(6) NOT NULL default '0',
sticky smallint(6) NOT NULL default '0',
votenum smallint(5) unsigned NOT NULL default '0',
votetotal smallint(5) unsigned NOT NULL default '0',
attach smallint(5) unsigned NOT NULL default '0',
similar varchar(55) NOT NULL default '',
PRIMARY KEY (threadid),
UNIQUE KEY threadid (threadid),
KEY postuserid (postuserid),
KEY pollid (pollid),
KEY forumid (forumid,visible,sticky,lastpost),
KEY lastpost (lastpost,forumid)
)
[/sql]

[AN-net]

[sql]
SELECT MAX(threadid) AS threadid FROM thread;
[/sql]

[Andreas]

Yeah, that gives me the ID - but I need the whole record

[AN-net]

maybe
[sql]
SELECT MAX(threadid) AS threadid, thread.x FROM thread;
[/sql]

[Andreas]

Does not work, as MAX() is a grouping function

[AN-net]

[sql]
SELECT MAX(threadid), title FROM thread;
[/sql]

Or maybe
[sql]
SELECT * FROM thread FORCE INDEX (indexname) ORDER BY threadid DESC LIMIT 1;
[/sql]