>> 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 ? > 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. 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 ? thanks, - Sam. PS : I bought your book by the way. Great read. 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. >
