[MySQL] How to make this in 1 querry?

[Chris]

I want to display last 10 rows but in inverted order (so with 100 rows 91-100 are returned but 91st first and 100th last). How to make it in one query?

Code: [Select]

$limit=10;

$count=mysql_result(mysql_query("SELECT COUNT(*) FROM ..."),0);
$start=$count-$limit;
if($start<0) $start=0;
mysql_query("SELECT * FROM ... ORDER BY id ASC LIMIT $start,$limit");


[JGadrow]

Code: (php) [Select]

$limit = 10;
$items = array();

$result = mysql_query('SELECT * FROM ... ORDER BY id DESC LIMIT %d', $limit);

for ($i = $limit - 1; $i >= 0 && ($row = mysql_fetch_array($result)); $i--)
{
    $items[$i] = $row;
}

[Chris]

But is there a way to make it on SQL side? I hoped to avoid creation of additional PHP array (no problem if there are 10 items but with more...)

If that's not possible, which is faster: additional COUNT(*) query (MyISAAM which has number of rows precomputed) or additional PHP array (without it there is no need for any array or other data manipulation, just one big string $string.=$row[]). Assume, let's say 100 items returned and eAccelerator+ZendOptimizer enabled.

[JGadrow]

Where's the additional array that you're mentioning? There's only a single array that's in use. Just instead of indexing it sequentially starting from zero, you index it starting from $limit - 1 and working down. This has the effect of sorting the values in the manner that you wish without adding too much additional processing than would otherwise be required.

The method that I posted would be fractions of a ms an increase over the standard db loading technique:

Code: (php) [Select]

$limit = 10;
$items = array();

$result = mysql_query('SELECT * FROM mytable LIMIT %d', $limit);

while ($row = mysql_fetch_array($result))
{
    $items[] = $row;
}

[Chris]

I do it this way.

Code: [Select]

$s='';
while ($row = mysql_fetch_array($result))
{
    $s.=$row;
}
echo $s; So no array and no second loop to display the results. Just one string that is later displayed.

[Nox]

http://php.net/manual/en/function.mysql-data-seek.php
Example is actually the same what you want ... I guess you can't do it much better that this

I don't think the was JG provided is significantly worse and I might have choose it for the sake of readability etc., but if you really want performance...

You might share some benchmarking afterwards


Edit: ok...you obviously CAN still do better yay

[JGadrow]

In the case of strings which are concatenated together, you could always just change:

Code: (php) [Select]

$s .= $row;
into:

Code: (php) [Select]

$s = $row . $s;
This is how you would accomplish reverse-placement using strings instead of arrays. Prepend instead of append.

[Chris]

Now that's the solution! Mere 2 chars more in the code to accomplish it

[Harkins]

Code: [Select]

select * from (select * from tablename order by whatever desc limit 10) temptable order by whatever asc;

Swap asc/desc based on whatever your actual order column is.

Looping over results just to throw them away makes baby jesus cry.

[Nox]

It's true that "sending data" part in SQL command execution was disproportionally largest item in the list when I looked at profiling recently... I dunno how much does this item affect crashability* (if the phase matters or not; I'd guess it does affect/doesn't matter) and unfortunately it was relatively tiny table&database and real ones might differ

*) relating to Chris' note that DB either runs fine and then crashes on contrary to the general image of DB slowing down

[JGadrow]

Looping over results just to throw them away makes baby jesus cry.

I re-read everything... who's throwing anything away? lol I think this is one of those times that actually needed a quote for clarity's sake.

[Harkins]

I glanced at your first message and misread it, sorry.