使用限制更快地通过SQL表导航

Question

My website has a menu with some items, and there are three pages. Using jQuery AJAX, I make a post request to a PHP script with the page number, page 1, page 2 or page 3.

I then use the following to select the 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 FROM market_items JOIN item_price ON market_items.market_hash_name=item_price.market_name WHERE inprogress='0' AND pending='0' AND avg_price_7_days >= '0.50' ORDER BY avg_price_7_days DESC LIMIT $start, $perpage";
$result = mysqli_query($conn, $sql);

As you can see, a post input of page=1 will display rows 1 to 56, page = 2 57 to 112, and so on.

The thing is, I have to make 3 queries to see 3 pages, one for each. Is there a faster way to do this? The lag to load a page is really quite noticeable (about 1.5 seconds to execute the query). I was thinking if I could execute the query once, and then navigate in the result set using mysqli_fetch_assoc($result)), if this is possible.

Answer 1

Here is your 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
FROM
    market_items
JOIN
    item_price
    ON
    market_items.market_hash_name=item_price.market_name
WHERE
    inprogress='0'
AND
    pending='0'
AND
    avg_price_7_days >= '0.50'
ORDER BY
    avg_price_7_days DESC
LIMIT
    $start, $perpage

First of all, injecting $start and $perpage is insecure, also run it when testing without cacheing, This will only work if you haven't cached it already

SELECT SQL_NO_CACHE ...

If you have ran the query you must clear the cache first,

RESET QUERY CACHE;

see also: Clear MySQL query cache without restarting server

Bench mark the speed, then test it without the order by bit. Make sure your indexing is proper. Make sure you have index on the sort field etc. Then if there is significant issue with sort in the query vs not sort, we may have to do more work.

You cant optimize the query by having it cached, it will always be fast after the first run.