order by ASC
in [General]
|
Prev: Shipping calculator
Next: Zend debugger doesn't work
From: "Daevid Vincent" on 19 Jan 2010 19:16 http://www.webdeveloper.com/forum/showthread.php?t=101174 You could do it like this too... ORDER BY `rollnumber` + 0 ASC And if you just got the data out in mysql (no ORDER BY -- which can be slow in mysql), you could use PHP's sort as well... http://php.net/manual/en/function.natsort.php > -----Original Message----- > From: Robert Cummings [mailto:robert(a)interjinn.com] > Sent: Saturday, January 16, 2010 9:37 PM > To: John Taylor-Johnston > Cc: PHP-General > Subject: Re: [PHP] order by ASC > > John Taylor-Johnston wrote: > > Did some googling. This worked: > > ORDER BY CAST(`rollnumber` AS SIGNED) > > > > What is the difference? My problem in the meanwhile must be > my version > > of MySQL? > > You could have skipped quotes altogether. The difference is > that you are > referencing a field name, not a string value. > > Cheers, > Rob. > -- > http://www.interjinn.com > Application and Templating Framework for PHP > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >
From: Ashley Sheridan on 19 Jan 2010 19:39 On Tue, 2010-01-19 at 16:16 -0800, Daevid Vincent wrote: > http://www.webdeveloper.com/forum/showthread.php?t=101174 > > You could do it like this too... > > ORDER BY `rollnumber` + 0 ASC > > And if you just got the data out in mysql (no ORDER BY -- which can be slow > in mysql), you could use PHP's sort as well... > > http://php.net/manual/en/function.natsort.php > > > -----Original Message----- > > From: Robert Cummings [mailto:robert(a)interjinn.com] > > Sent: Saturday, January 16, 2010 9:37 PM > > To: John Taylor-Johnston > > Cc: PHP-General > > Subject: Re: [PHP] order by ASC > > > > John Taylor-Johnston wrote: > > > Did some googling. This worked: > > > ORDER BY CAST(`rollnumber` AS SIGNED) > > > > > > What is the difference? My problem in the meanwhile must be > > my version > > > of MySQL? > > > > You could have skipped quotes altogether. The difference is > > that you are > > referencing a field name, not a string value. > > > > Cheers, > > Rob. > > -- > > http://www.interjinn.com > > Application and Templating Framework for PHP > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > I've always found that ordering is much faster in MySQL than PHP. You use quite a lot of memory sorting in PHP as well, as you first have to load the whole result set into an array, which is additional to any memory used in the result set by PHP and MySQL anyway. Thanks, Ash http://www.ashleysheridan.co.uk
From: "Daevid Vincent" on 19 Jan 2010 20:11 http://lmgtfy.com/?q=mysql+order+by+slow it's notoriously slow to use ORDER BY with large tables. Mainly b/c mySQL has to use a hash/temp table to re-sort AFAIK. I wasn't thinking of sorting the whole set, only the list of numbers as the OP only talked about sorting a single column... >>> rollnumber is a varchar(50). I need it to be a text field. ASC does >>> not order the way I want. >>> >>> 1000 >>> 1001 >>> 998 >>> 999 >>> >>> I want it to order like this: >>> >>> 998 >>> 999 >>> 1000 >>> 1001 _____ From: Ashley Sheridan [mailto:ash(a)ashleysheridan.co.uk] Sent: Tuesday, January 19, 2010 4:40 PM To: Daevid Vincent Cc: 'PHP-General' Subject: RE: [PHP] order by ASC On Tue, 2010-01-19 at 16:16 -0800, Daevid Vincent wrote: http://www.webdeveloper.com/forum/showthread.php?t=101174 You could do it like this too... ORDER BY `rollnumber` + 0 ASC And if you just got the data out in mysql (no ORDER BY -- which can be slow in mysql), you could use PHP's sort as well... http://php.net/manual/en/function.natsort.php > -----Original Message----- > From: Robert Cummings [mailto:robert(a)interjinn.com] > Sent: Saturday, January 16, 2010 9:37 PM > To: John Taylor-Johnston > Cc: PHP-General > Subject: Re: [PHP] order by ASC > > John Taylor-Johnston wrote: > > Did some googling. This worked: > > ORDER BY CAST(`rollnumber` AS SIGNED) > > > > What is the difference? My problem in the meanwhile must be > my version > > of MySQL? > > You could have skipped quotes altogether. The difference is > that you are > referencing a field name, not a string value. > > Cheers, > Rob. > -- > http://www.interjinn.com > Application and Templating Framework for PHP > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > I've always found that ordering is much faster in MySQL than PHP. You use quite a lot of memory sorting in PHP as well, as you first have to load the whole result set into an array, which is additional to any memory used in the result set by PHP and MySQL anyway. Thanks, Ash http://www.ashleysheridan.co.uk
From: Ashley Sheridan on 19 Jan 2010 20:22
On Tue, 2010-01-19 at 17:11 -0800, Daevid Vincent wrote: > http://lmgtfy.com/?q=mysql+order+by+slow > > it's notoriously slow to use ORDER BY with large tables. Mainly b/c mySQL > has to use a hash/temp table to re-sort AFAIK. > > I wasn't thinking of sorting the whole set, only the list of numbers as the > OP only talked about sorting a single column... > > >>> rollnumber is a varchar(50). I need it to be a text field. ASC does > >>> not order the way I want. > >>> > >>> 1000 > >>> 1001 > >>> 998 > >>> 999 > >>> > >>> I want it to order like this: > >>> > >>> 998 > >>> 999 > >>> 1000 > >>> 1001 > > > _____ > > From: Ashley Sheridan [mailto:ash(a)ashleysheridan.co.uk] > Sent: Tuesday, January 19, 2010 4:40 PM > To: Daevid Vincent > Cc: 'PHP-General' > Subject: RE: [PHP] order by ASC > > > On Tue, 2010-01-19 at 16:16 -0800, Daevid Vincent wrote: > > http://www.webdeveloper.com/forum/showthread.php?t=101174 > > > > You could do it like this too... > > > > ORDER BY `rollnumber` + 0 ASC > > > > And if you just got the data out in mysql (no ORDER BY -- which can be slow > > in mysql), you could use PHP's sort as well... > > > > http://php.net/manual/en/function.natsort.php > > > > > -----Original Message----- > > > From: Robert Cummings [mailto:robert(a)interjinn.com] > > > Sent: Saturday, January 16, 2010 9:37 PM > > > To: John Taylor-Johnston > > > Cc: PHP-General > > > Subject: Re: [PHP] order by ASC > > > > > > John Taylor-Johnston wrote: > > > > Did some googling. This worked: > > > > ORDER BY CAST(`rollnumber` AS SIGNED) > > > > > > > > What is the difference? My problem in the meanwhile must be > > > my version > > > > of MySQL? > > > > > > You could have skipped quotes altogether. The difference is > > > that you are > > > referencing a field name, not a string value. > > > > > > Cheers, > > > Rob. > > > -- > > > http://www.interjinn.com > > > Application and Templating Framework for PHP > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > I've always found that ordering is much faster in MySQL than PHP. You use > quite a lot of memory sorting in PHP as well, as you first have to load the > whole result set into an array, which is additional to any memory used in > the result set by PHP and MySQL anyway. > > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > > I had a look at some of the results from that link to Google, but I didn't see anything indicating MySQL was slower at ordering than PHP, only that MySQL has issues ordering very large query sets on queries that it cannot use an index to order on. I've tried optimising ordering in both PHP and MySQL, and found that usually it's best to leave as much to the database as possible, unless the ordering becomes too complex. Thanks, Ash http://www.ashleysheridan.co.uk |