[SOLVED] Mysql Circular index

[Rowst]

Hi,

 

I need to do an operation in mysql in the most efficient way possible. At the moment due to my lack of mysql knowledge i'm returning all rows from a table and manipulating them with php but I am worried that will be a bottle-neck as the table grows.

 

Here is the problem:

 

I have a table with a numerical id, and I want to find a row, then get the next 5 rows.

However, if there aren't 5 more rows because I am at the end of the table, I need to get the remainder from the head of the table.

 

eg: 1,2,3,4,5,6,7,8 starting from 3 gives 4,5,6,7,8

    1,2,3,4,5,6,7,8 starting from 5 gives 6,7,8,1,2

 

For bonus points i'd love it if 1,2,3,4,5 starting from 1 only gives 2,3,4,5 since otherwise it would be returning itself and that isn't really appropriate for my problem.

[bubblegum.anarchy]

Not one query but maybe some better ideas can be gleamed from the following:

<?php
$request_id = 5
$chunk_count = 5

$result = mysql_query("
   SELECT IF(".$chunk_count." - count(*) < 0, 0, ".$chunk_count." - count(*)) 
   FROM tablename 
   WHERE id >= ".$request_id." 
   ORDER BY id");

list($limit_count) = mysql_fetch_row($result);

$result = mysql_query("
   ( SELECT * FROM tablename WHERE id >= ".$request_id." ORDER BY id LIMIT ".$chunk_count." )
  UNION 
   ( SELECT * FROM tablename ORDER BY id LIMIT ".$limit_count." )
");
?>

[Rowst]

That's set me in the right definitely set me in the right direction.

 

Thanks.