Sam, I am constantly coding with PHP and MySQL. As far as: >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 >o ORDER BY so that FILESORT or TEMPORARY can be avoided ?
I have learned to let MySQL do anything DB related. Generally, whenever you can have MySQL do the work you are better off than letting PHP do it - unless you need some type of error checking or logging. Rob -----Original Message----- From: Sam Iam [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 9:53 AM To: Paul DuBois Cc: MySQL List Subject: Re: Can you skip ORDER BY & get rows back in inserted order ? >> 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. > --------------------------------------------------------------------- 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
