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

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.

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.

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.