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.
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.
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
JOINs. (Actually, your
JOINmay 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
- Something that might help is getting rid of
categoriesand simply having
- 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
(At least you are not using the EAV (open-ended key-value) schema design; it is much worse at optimizing.)
Answered By – Rick James