Single Query SELECT last 10 rows ORDER BY ASC?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by Eran »

I think it would vary greatly as the table size increases (the PHP sort will remain the same for the same amount of rows). Also, it's supposed to be (I think) for a small amount of rows (~10) .
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

pytrin wrote:I think it would vary greatly as the table size increases (the PHP sort will remain the same for the same amount of rows).
The same applies to the SQL solution - the second ORDER BY operates only on the amount of rows limited by LIMIT X - just like the PHP solution. And the subselect query is used by both solutions.
pytrin wrote:Also, it's supposed to be (I think) for a small amount of rows (~10) .
I expected that increasing the count of SQL rows should be better for the PHP solution than for the SQL solution, because the data returned by the subselect could not be indexed and the sorting process should be slower (i.e. SQL sort vs. PHP array_reverse - no sorting).
The results are just the opposite to my expectations - for 10 rows limit we have:
0.00058698654174805
0.00037217140197754
I.e. < 2 times difference.

PS: In fact, it's not array_reverse($a) which slows the execution but the while loop for prefetching all rows.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

Ooops :oops: I have a SQL error in my query :)
So, it should be:

Code: Select all

function microtime_float() 
{ 
    list($usec, $sec) = explode(" ", microtime()); 
    return ((float)$usec + (float)$sec); 
} 
 
 
$time_start = microtime_float();
$result = mysql_query('
    select 
        * 
    from 
        b 
    order by 
        val desc 
    limit 500
');
 
$a = array();
while($row = mysql_fetch_assoc($result))
    $a[] = $row;
$time_end = microtime_float();
$time = $time_end - $time_start;
echo "$time <br />";
array_reverse($a);
 
$time_start = microtime_float();
$result = mysql_query('
    select 
        * 
    from  
        (
            select 
                * 
            from 
                b 
            order by 
                val desc 
            limit 500
        ) as t
    order by 
        val asc
');
$time_end = microtime_float();
$time = $time_end - $time_start;
echo "$time <br />";
Result for LIMIT 500:
0.003119945526123
0.0027260780334473
Results for LIMIT 10:
0.0007169246673584
0.00070405006408691
So, for 70'000 rows table there is almost no difference :)
Sorry for my mistake.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by Eran »

Basically the same. That's good to know :)
Also the tidbit about populating large arrays using foreach. Interesting.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by jaoudestudios »

Wow nice guys. You beat me to it. But good results!
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by JAB Creations »

jaoudestudios, there seems to be a bit of interest in a chat room I'm working on. This morning I got to the point where (if I'm signed on both Firefox and Opera) I can send a message from either or and see it appear in a second or two later in the other browser. The active members list also works (though for testing the live code is commented out to keep me always signed in though it wouldn't take half a second to restore the live functionality. No bandwidth is burned if there are no messages and I've really minimized transfers. The JavaScript file itself is 22.5KB right now and fluctuates a few KB. Compressed (right now just out of curiosity) it's 4402 bytes which should load in a second flat on a solid 36K modem (at 4.7KB a sec). There is some bloat too so the size may get a wee bit smaller...plus that includes the work arounds for IE. Also another goal of mine has been absolutely no errors or warnings in any console for any browser (where the chat room is supported at least).

Here are the chat commands that are (currently) intercepted by JavaScript and are not sent to the server...
chat - Layer: Shows the chat room.
help - Layer: Shows the help layer without interrupting the chat room in the background.
rules - Layer: Shows the chat rules without interrupting the chat room in the background.
-, up - scrolls the chat up a single page.
+, down - Scrolls the chat down a single page.
{, begin, top - Scrolls to the very top of the chat room.
}, end, bottom - Scrolls to the very bottom of the chat room.
list, ls, dir - Manually updates the chat room list (people in the chat room).
cls, clear - Deletes all the content in the chat room (at the client, not from the server, not implemented just yet).
exit - A confirm JavaScript asks if you want to close the chat room.
break - Secret: You hear, 'Oh I just fixed that!' by Brock Samson and a broken glass PNG is set to the background image.
fix - Secret: Fixes what you break. ;-)
spiked walls Secret: Makes spiked walls on the chat list and you hear Dr. Orpheus and Dr. Venture talking about the speed of spiked walls.

It works in Opera 8.02+(earliest version with AJAX support), IE 5.5+ (IE 5.0 support is dependent on a fix for some other reusable functions that don't exist exclusively for the chat room), and pretty much any other browser that supports AJAX. I plan on using it as part of my business suite so that is why I'm bent on IE computability (otherwise to hell with IE6 and older!)

Tonight I have to work on some bugs where it doesn't add up the row ID's of the messages correctly...once I fix that the main functionality will be complete! I've also gone to great lengths to make sure the chat room works smoothly if you close it and then open it again without reloading the page (so the AJAX requests halt if it's not open but start again when the chat() is called...though without recreating the chat room XHTML elements.

I've also put as much effort to offload as much as I can to the client. So besides some very minimal PHP and MySQL even things I haven't done (such as harsh cursing censorship) will be handled entirely by the client. Someone mentioned that a flat file database would be more efficient, so I'm wondering what the debating titans have to say about that? :mrgreen:

Also if you guys want I'd be more then happy to shoot a short video and post it on my server of the chat room in action after I've fixed these last bugs and have the critical functionality working fine.

Here are some development screenshots (nothing of late since it's all mostly non-visual)...
http://www.jabcreations.com/temp/chat/
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by jaoudestudios »

Wow you are way ahead of me.

Its sounding & looking good.

Let us know when we can test the beta :)
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by JAB Creations »

1.) http://www.jabcreations.com/members/

2.) Sign in as test / test.

3.) Click on the chat room anchor at the top left.

4.) No swearing, type help, rules, and chat for the main layers.

It's not complete and some things aren't enabled at the moment (such as the dynamic who's online list) which I may fix layer before I go to bed. Well enable really, it may have bugs though worked just fine when I last tested it.

This should also work in IE 5.5+ though it's not scrolling in 5.5 right now but it is automatically scrolling in IE6+. It only auto-scrolls of course if the chat room is already at the bottom. When it first starts it will auto-scroll too. Be sure to make sure you can hear sound if you type some of the secret commands. :wink:

More to come... :)
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by jaoudestudios »

Just gave the chat a test with JAB, VERY IMPRESSIVE! Well done! Looking forward to seeing the final product.
Post Reply