php - Faster navigation through SQL table using limits -

my website has menu items, , there 3 pages. using jquery ajax, make post request php script page number, page 1, page 2 or page 3.

i use following select appropriate rows.

$page = intval($_post["page"]); $perpage = 56;   $calc = $perpage * $page; $start = $calc - $perpage;  $sql = "select market_items.id, market_items.market_hash_name, market_items.icon_url_large, market_items.name_color, market_items.inprogress, item_price.market_name, item_price.avg_price_7_days market_items join item_price on market_items.market_hash_name=item_price.market_name inprogress='0' , pending='0' , avg_price_7_days >= '0.50' order avg_price_7_days desc limit $start, $perpage"; $result = mysqli_query($conn, $sql); 

as can see, post input of page=1 display rows 1 56, page = 2 57 112, , on.

the thing is, have make 3 queries see 3 pages, 1 each. there faster way this? lag load page quite noticeable (about 1.5 seconds execute query). thinking if execute query once, , then navigate in result set using mysqli_fetch_assoc($result)), if possible.

here original query,

select     market_items.id,     market_items.market_hash_name,     market_items.icon_url_large,     market_items.name_color,     market_items.inprogress,     item_price.market_name,     item_price.avg_price_7_days     market_items join     item_price     on     market_items.market_hash_name=item_price.market_name     inprogress='0' ,     pending='0' ,     avg_price_7_days >= '0.50' order     avg_price_7_days desc limit     $start, $perpage 

first of all, injecting $start , $perpage insecure, run when testing without cacheing, work if haven't cached already

select sql_no_cache ... 

if have ran query must clear cache first,

reset query cache; 

see also: clear mysql query cache without restarting server

bench mark speed, test without order bit. make sure indexing proper. make sure have index on sort field etc. if there significant issue sort in query vs not sort, may have more work.

you cant optimize query having cached, fast after first run.