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.
Comments
Post a Comment