List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:July 12 2001 1:36pm
Subject:Re: why doesn't Mysql use INDEX for ORDER BY ?
View as plain text  
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 <mysql-thread79247@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
mysql doesn't use INDEX for ORDER BY with LIMIT and no WHEREGunnar von Boehn11 Jul
  • Re: why doesn't Mysql use INDEX for ORDER BY ?Gunnar von Boehn12 Jul
  • Re: why doesn't Mysql use INDEX for ORDER BY ?Gerald Clark12 Jul
    • Re: Mysql bug, doesnt use INDEX for ORDER BY!!Gunnar von Boehn13 Jul
      • Re: Mysql bug, doesnt use INDEX for ORDER BY!!Benjamin Pflugmann16 Jul
        • Re: Mysql bug, doesnt use INDEX for ORDER BY!!Gunnar von Boehn23 Jul