0

I've the following products table

CREATE TABLE `tmp_products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `parent_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 `brand_id` int(11) NOT NULL,
 `store_id` int(11) NOT NULL,
 `title` varchar(250) NOT NULL,
 `price` decimal(10,0) NOT NULL,
 `sale_price` decimal(10,0) NOT NULL,
 `discount_value` decimal(10,0) NOT NULL,
 `discount_percentage` decimal(10,0) NOT NULL,
 `color` varchar(64) NOT NULL,
 `size` varchar(64) NOT NULL,
 `weight` decimal(10,0) NOT NULL,
 `image` text NOT NULL,
 `fast_shipping` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `parent_id` (`parent_id`),
 KEY `category_id` (`category_id`),
 KEY `brand_id` (`brand_id`),
 KEY `sale_price` (`sale_price`),
 KEY `discount_percentage` (`discount_percentage`),
 KEY `color` (`color`),
 KEY `size` (`size`),
 KEY `weight` (`weight`),
 KEY `store_id` (`store_id`),
 KEY `fast_shipping` (`fast_shipping`),
 FULLTEXT KEY `title` (`title`)
) ENGINE=InnoDB

And there is another product_categories table

CREATE TABLE `product_categories` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `product_id` (`product_id`),
 KEY `category_id` (`category_id`)
) ENGINE=InnoDB

I may filter the data with any of the previous columns in the products table and all of it is or/and clause based on customer filtration like:

category_id: is joined with the product_categories if the customer hits the category page

brand_id could be used in the brand page or as a filter in search or category page.

sale_price: could be used in filtering products.

title: full text search index.

fast_shipping: a flag with 0 and 1 values to indicate the products with fast shipping.

store_id: mainly used to get info about the store that owns that product and and also is used to join with countries table to get the store country.

Stores table has an indexed country_id for countries table

Most of the above columns would be used also in ordering and the parent_id will be used if there is no filter by color and size.

The question here is there a better way to optimize my select query to make it faster as i'm dealing with some millions of products here.

So, do i've to use any compound indexes instead of single indexes to make my query faster?

Edit

Some samples of queries that may run based on customer filtration:

A query for fetching products for a table:

select `p`.`id`, `p`.`fast_shipping`, `p`.`category_id`, `p`.`weight`,  `p`.`title`, `p`.`price`, `p`.`sale_price`, `p`.`discount_percentage`, `p`.`image`, `p`.`color`, `p`.`size`,     
CONCAT(u.hash, "/", u.name) AS country_flag     
from `products` as `p`     
inner join `stores` as `s` on `s`.`id` = `p`.`store_id`     
inner join `countries` as `c` on `c`.`id` = `s`.`country_id`     
left join `uploads` as `u` on `u`.`id` = `c`.`flag`     
where p.id IN (SELECT product_id FROM product_categories WHERE category_id = 395)     
and `p`.`parent_id` = 0 order by `p`.`id` desc limit 36 offset 0

Query to add more filter by price

select `p`.`id`, `p`.`fast_shipping`, `p`.`category_id`, `p`.`weight`,  `p`.`title`, `p`.`price`, `p`.`sale_price`, `p`.`discount_percentage`, `p`.`image`, `p`.`color`, `p`.`size`,     
CONCAT(u.hash, "/", u.name) AS country_flag     
from `products` as `p`     
inner join `stores` as `s` on `s`.`id` = `p`.`store_id`     
inner join `countries` as `c` on `c`.`id` = `s`.`country_id`     
left join `uploads` as `u` on `u`.`id` = `c`.`flag`     
where p.id IN (SELECT product_id FROM product_categories WHERE category_id = 395)     
and `p`.`parent_id` = 0 
and `p`.`sale_price` >= 20 and `p`.`sale_price` <= 120

order by p.id desc limit 36 offset 0

0

The question here is there a better way to optimize my select query to make it faster as i'm dealing with some millions of products here.

well there isnt something called better way and it depends on every query, you didnt show us any queries.

you can use compound indexes when you have several column in where conditions, for example if you create index on (id,category_id,brand_id ) and where condition you have only id, category_id, the index will be used.

  • I've edited my question. – Hasan Zohdy Jul 27 '18 at 10:58
  • well the query is taking time ? how much data are there in the tables ? you can create index on (id,flag) and (id,country_id) – moudiz Jul 27 '18 at 11:16
  • There is already indexes for each column..separately but not compound ones. There is about 2.5m products and query takes about 2-4 seconds. – Hasan Zohdy Jul 27 '18 at 11:20
  • The query will read one index , you can get the query plan for the query and check which index is reading and its cost. anyway in your scenario you can add a compound index. the best way to make sure is to test it @HasanZohdy – moudiz Jul 27 '18 at 11:25
0

The first thing is to rid of the IN clause and the subselect. Both of them can be replaced by single JOIN:

SELECT p.id
     , p.fast_shipping
     , p.category_id
     , p.weight
     , p.title
     , p.price
     , p.sale_price
     , p.discount_percentage
     , p.image
     , p.color
     , p.size
     , CONCAT(u.hash, "/", u.name) AS country_flag     

  FROM      products      AS p     
-- Here is the replacement for "IN (SELECT.." and "WHERE"
-- It's placed immediately after the `products` table to reduce the derived table size
  JOIN product_categories AS pc  ON pc.product_id = p.id 
                                AND pc.category_id = 395
                                AND p.parent_id = 0 
-- All the rest JOINs. `INNER JOIN..ON` is the same as `JOIN..ON` for mysql.
  JOIN stores             AS s   ON s.id = p.store_id
  JOIN countries          AS c   ON c.id = s.country_id     
  LEFT JOIN uploads       AS u   ON u.id = c.flag

-- WHERE is trimmed out completely
 ORDER BY p.id DESC 
 LIMIT 36 OFFSET 0
;

For the given query you need the next multicolumn indexes:

  • table products - (parent_id, store_id)
  • table product_categories - (product_id, category_id)
  • table stores - (id, country_id) but simple (country_id) is enough if id is the PK for stores table

The second query can be modified the same way, except the products table now need an index (parent_id, store_id, sale_price):

SELECT p.id
     , p.fast_shipping
     , p.category_id
     , p.weight
     , p.title
     , p.price
     , p.sale_price
     , p.discount_percentage
     , p.image
     , p.color
     , p.size
     , CONCAT(u.hash, "/", u.name) AS country_flag     

  FROM products           AS p
  JOIN product_categories AS pc ON pc.product_id = p.id
                               AND pc.category_id = 395
                               AND p.parent_id = 0 
                               AND p.sale_price BETWEEN 20 AND 120

  JOIN stores             AS s  ON s.id = p.store_id
  JOIN countries          AS c  ON c.id = s.country_id
  LEFT JOIN uploads       AS u  ON u.id = c.flag
;

As far as (parent_id, store_id, sale_price) index can replace the (parent_id, store_id) one you need only (parent_id, store_id, sale_price), not both.

  • Thanks for the great answer, but what if there is a search by color and/or size, also discount_percentage may or may not be used for filtering info, same applies on fast_shipping – Hasan Zohdy Jul 27 '18 at 21:38
  • In general, all the columns mentioned in the JOIN..ON / WHERE / GROUP BY / ORDER BY / etc should be indexed. If more than one column from the same table is involved all of them should be present in the multicolumn index. The bad news is that order columns are used for index is very significant. Three columns can be arranged in 6 ways. Four columns - in 24 ways. And so forth - factorial (n!). And there is no common rules to determine which order is the best. Even worst - different queries can require the different indexes - (a,b,c) and (c,a,b) for example. – Kondybas Jul 27 '18 at 22:00
  • So should i add more composite indexes for each combination? For now i've only single indexes for each column and i'm placing the conditions in a certain order based on customer filtration but the issue here is that i should do more indexes based on the given filter combinations, right? – Hasan Zohdy Jul 27 '18 at 22:15
  • Each extra index has an overhead. On the every INSERT/UPDATE/DELETE all related indexes should be updated. Also each index consumes the disk space and RAM that can drive to the overall slowdown. We can't create all possible indexes for each occasion. All that we can do is to restrict queries to some subset covered by indexes. Where is the balance depends on certain project. – Kondybas Jul 27 '18 at 22:31
  • The other three query types won't be the problem as i know the indexes will affect them, so i will just have to stick with single indexes now as best solution? – Hasan Zohdy Jul 27 '18 at 23:12
0

Please clean up the tables. product_categories with product_id and category_id implies a many:many mapping between products and categories.

But the existence of category_id in tmp_products implies that a product belongs to only one category. This contradicts the many:many concept.

Which is it? Maybe tmp_products needs main_category_id? Or it is relation is not really many:many?

I also see parent_id, which implies a hierarchy in tmp_products. Is that correct? Is it relevant to this question?

As for 'proper indexing',...

  • This discusses optimal indexes for a many:many table (get rid of auto_inc; use composite PK, etc).
  • Do not index flags; such indexes won't be used due to poor cardinality.
  • Learn to use "composite" indexes where appropriate.
  • When using MATCH in the WHERE clause of a SELECT, the FULLTEXT index will be the only index used.

Assuming

               ON pc.product_id = p.id
              AND pc.category_id = 395
              AND p.parent_id = 0 
              AND p.sale_price BETWEEN 20 AND 120

then have these composite indexes:

p:  PRIMARY KEY(id)   -- (already exists)
p:  INDEX(parent_id, sale_price)
pc: INDEX(category_id, product_id)  -- (as mentioned above)

This set of indexes will happen to work whether or not you include the sale_price limitation. Caveat: If you have other filtering, other indexes may be needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.