"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
SELECT * FROM table ORDER BY id DESC LIMIT 1

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 ...

EXPLAIN SELECT * FROM thread ORDER BY threadid DESC LIMIT 1


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

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)
)

[AN-net]

SELECT MAX(threadid) AS threadid FROM thread;

[Andreas]

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

[AN-net]

maybe

SELECT MAX(threadid) AS threadid, thread.x FROM thread;

[Andreas]

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

[AN-net]

SELECT MAX(threadid), title FROM thread;


Or maybe

SELECT * FROM thread FORCE INDEX (indexname) ORDER BY threadid DESC LIMIT 1;

[Andreas]

1) Does not work becasue MAX() is a grouping function
2) Does work, but gives the same output as in Post #3, eg. Table Scan :(

[AN-net]

interesting as mysql.com said using force index makes it so it uses indexes only and requires no table scan>_>

maybe substitute FORCE INDEX with USE INDEX ;)

[Marco van Herwaarden]

select * from post where postid = (select max(postid) from post)But i think this will only work for MySQL 4.1 and up.

Otherwise create 2 queries: 1 to get max postid, and 1 to retrieve the row.

Ohh my example is for post table, but should be the same for thread.

Also please notice that you should check with a fully loaded table, since the optimizer might decide that if there are only a few rows, it is more efficient to use a full table scan then to use the index.

[Paul M]

Hmm ...

EXPLAIN SELECT * FROM thread ORDER BY threadid DESC LIMIT 1


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?If I were you I would check/rebuild your indexes, you should get this ;

EXPLAIN SELECT * FROM thread ORDER BY threadid DESC LIMIT 1
table type possible_keys key key_len ref rows Extra
thread index NULL PRIMARY 4 NULL 82

[Princeton]

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
SELECT * FROM table ORDER BY id DESC LIMIT 1

But this causes large table scans ... :(

since there is no condition (WHERE and/or GROUP BY) - this query will be a full index scan

[Marco van Herwaarden]

since there is no condition (WHERE and/or GROUP BY) - this query will be a full index scan
But still could use the index for ordering.