It may be the DESC that prohibits the use of the index.
Try an normal ascending order.
Gunnar von Boehn wrote:
> 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
--
Gerald L. Clark
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
