List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 5 2002 6:00pm
Subject:Re: Can you skip ORDER BY & get rows back in inserted order ?
View as plain text  
At 9:52 -0800 3/5/02, Sam Iam wrote:
>  >> Is there some way that I can avoid doing an ORDER BY and get my
>  >> rows back ordered by album_id, rank they way I inserted them ?
>
>  >No.  That is the nature of relational databases.
>
>Is this because the index cache in ram may not be loaded in order ?

No, it's because relational engines consider a bunch of rows as a set,
and "order" is not a property that sets have.  Thus, such engines make no
guarantee about the order in which they'll return a set, unless you
specify an ORDER BY clause to provide sorting instructions.

>
>  > You might want to consider adding an AUTO_INCREMENT column, then inserting
>  >in the order you want them back.  Then you can retrieve in that 
>order by using
>  >an ORDER BY on the AUTO_INCREMENT column.
>
>My "rank" column is the order that I want them back in.

Then use it for sorting.  Won't that give you the result you want?

>
>Would you agree that sorting small row sets on the client with say 
>PHP is more efficient than asking a busy MySQL server with many 
>clients to do ORDER BY so that FILESORT or TEMPORARY can be avoided ?

That's subject to empirical test. :-)

In general, the MySQL server's built to be able to sort efficiently, so
you may as well let it do the sorting.  Sorting "on the client" may not
gain you anything if the web server and the MySQL server are both running
on the same host, which is common.  In that case, the work's all being done
on the same machine anyway?

>
>thanks,
>
>- Sam.
>
>PS : I bought your book by the way. Great read.

Thanks.  Which book?  The doorstop, or the MySQL/Perl book?

>
>On Tuesday, March 5, 2002, at 09:12 AM, Paul DuBois wrote:
>
>>At 8:16 -0800 3/5/02, Sam Lam wrote:
>>>I have a table like so :
>>>
>>>CREATE TABLE album_rank(
>>>	album_id INT NOT NULL,
>>>	rank INT NOT NULL,
>>>	KEY(album_id)
>>>)
>>>
>>>I want to query on the album_id & get the results ordered by rank 
>>>but I want to avoid doing an ORDER BY in the query because of the 
>>>filesort that it usually triggers so I pre-ordered the rows in my 
>>>table by inserting them in the order I wanted with a CREATE TABLE 
>>>ordered SELECT * FROM unordered ORDER BY album_id, rank ASC.
>>>
>>>For some reason I get the data back in a different order. I've 
>>>tried ORDER BY album_id,rank ASC & DESC in case it's a FILO or 
>>>FIFO.
>>>
>>>Is there some way that I can avoid doing an ORDER BY and get my
>>>rows back ordered by album_id, rank they way I inserted them ?
>>
>>No.  That is the nature of relational databases.
>>
>>You might want to consider adding an AUTO_INCREMENT column, then inserting
>>in the order you want them back.  Then you can retrieve in that 
>>order by using
>>an ORDER BY on the AUTO_INCREMENT column.

Thread
Can you skip ORDER BY & get rows back in inserted order ?Sam Lam5 Mar
  • Re: Can you skip ORDER BY & get rows back in inserted order ?Paul DuBois5 Mar
    • Re: Can you skip ORDER BY & get rows back in inserted order ?Sam Iam5 Mar
      • Re: Can you skip ORDER BY & get rows back in inserted order ?Paul DuBois5 Mar
      • RE: Can you skip ORDER BY & get rows back in inserted order ?Rob Emerick5 Mar