RE: Can you skip ORDER BY & get rows back in inserted order ?
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
Re: Can you skip ORDER BY & get rows back in inserted order ?
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. - 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
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. >
Re: Can you skip ORDER BY & get rows back in inserted order ?
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
