"ORDER BY" without large table scans and filesort

[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]

[sql]select * from post where postid = (select max(postid) from post)[/sql]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]

Originally Posted by KirbyDE

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?

If I were you I would check/rebuild your indexes, you should get this ;

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

table	type	possible_keys	key  	key_len	ref	rows	Extra  
thread	index	NULL		PRIMARY	4	NULL	82

[Princeton]

Originally Posted by KirbyDE

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

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

[Marco van Herwaarden]

Originally Posted by princeton

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.