Faster navigation through SQL table using limits

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.

Just a quick point based on using AJAX, make sure you have your page one display without AJAX else your site will not get spidered and will just be a template with none of your content inside. — Liam Sorsby, Jul 23 '15 at 15:00
@LiamSorsby this is no problem since this is a login only page. — Semger, Jul 23 '15 at 15:01
1.5 seconds for a simple query? Is this on a shared server? I'm using an orm with no caching, with a SELECT query on 1000 results which takes 0.044 seconds. Are you talking about the display time or the query execution time? — Liam Sorsby, Jul 23 '15 at 15:01
@LiamSorsby, i think it's more like half a second, but it is a VPS :/ — Semger, Jul 23 '15 at 15:03
Do you have indexes set up? Make sure market_hash_name and market_name are indexed. — eagle12, Jul 23 '15 at 15:03
Caching, maybe memcached. Although managing the cache will be quite complicated. — frz3993, Jul 23 '15 at 15:03
Do you have a local testing environment you can look run the scripts on to test the speed? If this is on a VPN I'd imagine it's more a case of the server load being rather high. — Liam Sorsby, Jul 23 '15 at 15:05
I don't think it is either possible or desirable to try keeping a database connection open while waiting for a user to thumb to the next page. Even if possible, imagine what would happen if you had thousands of users each trying to hog your MySQL connection pool. — Tim Biegeleisen, Jul 23 '15 at 15:05
The reason for the slowness of queries of this type is using sort on them, this makes the database do a full table scan regardless of the limit. Think about it, how can it do the limit before it orders the data in the correct way. The best way around this is to do a sub-query fetching just the id's in the correct order then join on that to get the rest of the data. That way any temporary tables needed for the sort are using the least amount of data, and stand a better chance of not hitting the disc — ArtisticPhoenix, Jul 23 '15 at 15:11
It's also best to execute just the query in something like phpmyadmin, without query caching, to see actually query time when benchmarking execution time. — ArtisticPhoenix, Jul 23 '15 at 15:16
@ArtisiticPhoenix The first time this query is made it takes 0.4 seconds, then 0.0002 seconds. Can you show me what you mean by getting the ids and making a sub query? Maybe submit is as an asnwer so i could accept it. — Semger, Jul 23 '15 at 15:21
@Semger - the second time it is cached, use `SELECT SQL_NO_CACHE ..` instead — ArtisticPhoenix, Jul 23 '15 at 15:24

score 0 · Answer 1 · edited May 23 '17 at 10:24

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.

Faster navigation through SQL table using limits

1 Answers1