How to make SQL query faster with paging?

Issue

I have an ecommerce website with a shop page, each shop page contain 6 products and user can filter according to size and category, then user must press next to get next 6 products. google pagespeed index says that TTFB is about 0.7 seconds which is mostly taken by SQL query yet i have only 23 product which i expect to increase dramatically when i add more products.
mysql query looks like this in php file.

$q = "
    SELECT  products.name, products.colors, products.sizes,
            products.selling_price,
            products.id, products.size_chart, products.about
        FROM  products 
";
$q .= $filter_options;

$filter_options is returned from another function which makes the entire query to be like this on first page (page 1) when i echo $q without applying size and category filters only sorting.

    SELECT  products.name, products.colors, products.sizes,
            products.selling_price,
            products.id, products.size_chart, products.about
        FROM  products
        INNER JOIN  category  ON category.id = products.category_id
        WHERE  1 = 1
        ORDER BY  products.selling_price Desc
        LIMIT  0,6 

and here is the query with category and size filters are applied

    SELECT  products.name, products.colors, products.sizes,
            products.selling_price,
            products.id, products.size_chart, products.about
        FROM  products
        INNER JOIN  category  ON category.id = products.category_id
        WHERE  1 = 1
          AND  FIND_IN_SET('s', products.sizes)>0
          AND  category.name = 'Dresses'
        ORDER BY  products.selling_price Desc
        LIMIT  0,6 

My limitation are:

i cant limit number of results by WHERE products.id < $some_page_index since products ID are not continous since i add and remove products directly which makes id like 1 2 4 10 so it will result in less than 6 products on some pages.

Solution

If you are doing "pagination", don’t use OFFSET, instead "remember where you left off": Pagination

Do you at least have these indexes?

category:  INDEX(name)
products:  INDEX(category_id, selling_price)

I recommend looking over the queries that are used, then building composite indexes starting with the column(s) tested with =.

There are practical limits on how many columns in an index and how many different indexes to have. If you have INDEX(a,b), do not also have INDEX(a). More: Index Cookbook

MySQL cannot provide a perfect indexing mechanism for an app like yours.

More

I should point out that seemingly minor changes to the query and table schema may have drastic impact on the query, especially wrt paginating.

If there are only 6-7 "categories", indexing that table is unlikely to have a noticeable effect. (Still, I recommend adding UNIQUE(name).)

The big problem in paginating of the given query is that all the possible rows are located, then sorted, then the desired 6 are delivered to the user. So, there is not a lot of difference for page 1 versus page 100.

To make page 1 run faster than that, there needs to be an INDEX that handles all of the WHERE plus handles the ORDER BY. With such, the 6 rows can [probably] be found with without looking at the entire table an sorting it.

Things that prevent that desirable optimization:

  • Non-sargable expressions in WHERE (such as FIND_IN_SET(...))
  • JOINs. (Actually, your JOIN may be an exception — I think it will start by scanning that tiny table for all ids of Dresses, then use them (without realizing that there is only one) for starting a partial scan of products.
  • Something that might help is getting rid of categories and simply having category_name in products instead ofcategory_id.
  • Note that a composite index such as (category, selling_price) can be efficiently used if it knows that there is only one category. That way it can be more efficient by then moving on to the range requested for sellint_price.

(At least you are not using the EAV (open-ended key-value) schema design; it is much worse at optimizing.)

Answered By – Rick James

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published