Could somebody please explain to me
why mysql doesn't use an INDEX for ORDER BY
in joined selects with LIMT and no WHERE clause.
thanks
Gunnar von Boehn
On Mit, 11 Jul 2001, Gunnar von Boehn wrote:
>using mysqlversion 2.23.30
>
>I have the problem that mysql doesn't want to use an index.
>I tried USE INDEX() but that doesn't help either.
>
>I have a tables with 250k rows
> person(
> id int, (indexed)
> name varchar(40),
> fatherid int,
> motherid int,
> creationtime timestamp (indexed)
> )
>
>
>I want to see the last 10 persons and their parents that are added to my database.
>
> SELECT p.name, father.name as fathername, mother.name as mothername
> FROM person as p
> LEFT JOIN person as father ON (p.fatherid=father.id)
> LEFT JOIN person as mother ON (p.motherid=mother.id)
> ORDER BY p.creationtime DESC LIMIT 10;
>
>I thought that mysql would use the INDEX on creationtime to easely
>find the 10 rows and than use the other indexes to fetch their parents.
>But mysql preferes to scan the whole table (250K rows) and than to sort the result.
>This is of course very,very slow.
---------------------------------------------------------------------
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
