0

OK here is my situation,

I've a 'products' table that contains my 'parent' products info. And a 'product_prices' table that contains the parent product id and its full info The website has different pages types to display products, one of them is 'landing pages' that contains custom products with main title for that landing page and the last table here is the 'landing_page_products' that contains all parent products ids

Here is the full structure for the 4 tables:

--
-- Table structure for table `landing_pages`
--

CREATE TABLE `landing_pages` (
    `id` int(10) UNSIGNED NOT NULL,
    `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `description` text COLLATE utf8_unicode_ci NOT NULL,
    `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `deleted_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `landing_page_products`
--

CREATE TABLE `landing_page_products` (
    `id` int(10) UNSIGNED NOT NULL,
    `landing_page_id` int(10) UNSIGNED NOT NULL DEFAULT '1',
    `product_id` int(10) UNSIGNED NOT NULL DEFAULT '1',
    `deleted_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
    `id` int(10) UNSIGNED NOT NULL,
    `store_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
    `category_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
    `brand_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
    `deleted_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `lowest_price` double NOT NULL,
    `highest_price` double NOT NULL,
    `store_product_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `store_parent_product_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `fast_shipping` tinyint(1) NOT NULL,
    `label` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_prices`
--

CREATE TABLE `product_prices` (
    `id` int(10) UNSIGNED NOT NULL,
    `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
    `color` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `price` double(8,2) NOT NULL,
    `discount_value` double(8,2) NOT NULL,
    `discount_percentage` double(8,2) NOT NULL,
    `deleted_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `sale_price` double NOT NULL,
    `other_options` text COLLATE utf8_unicode_ci NOT NULL,
    `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `store_product_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `description` text COLLATE utf8_unicode_ci NOT NULL,
    `keywords` text COLLATE utf8_unicode_ci NOT NULL,
    `image` text COLLATE utf8_unicode_ci NOT NULL,
    `images` text COLLATE utf8_unicode_ci NOT NULL,
    `weight` double(8,2) NOT NULL,
    `upc` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `product_url` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `landing_pages`
--
ALTER TABLE `landing_pages`
    ADD PRIMARY KEY (`id`);

--
-- Indexes for table `landing_page_products`
--
ALTER TABLE `landing_page_products`
    ADD PRIMARY KEY (`id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
    ADD PRIMARY KEY (`id`);

--
-- Indexes for table `product_prices`
--
ALTER TABLE `product_prices`
    ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `landing_pages`
--
ALTER TABLE `landing_pages`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=67;
--
-- AUTO_INCREMENT for table `landing_page_products`
--
ALTER TABLE `landing_page_products`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22052;
--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48904;
--
-- AUTO_INCREMENT for table `product_prices`
--
ALTER TABLE `product_prices`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=248270;

Total records in each table: landing_pages: 66 landing_page_products: 22050 "22K" products: 48903 "49K" product_prices: 248269 "248K"

When i run the following query, it takes about 7-8 seconds

select `p`.`id`, `pp`.`title`, `pp`.`price`, `pp`.`sale_price`, `pp`.`discount_percentage`, `pp`.`image`, 
(SELECT MIN(p2.sale_price) FROM products as p2 WHERE p.id = p2.product_id) as lowest_price, 
(SELECT MAX(p3.sale_price) FROM products as p3 WHERE p.id = p3.product_id) as highest_price 
from `products` as `p` 
left join `product_prices` as `pp` on `p`.`id` = `pp`.`product_id` 
where `p`.`id` in (select `pt`.`product_id` from `landing_page_products` as `pt` where `pt`.`landing_page_id` in (18)) 
group by `p`.`id` 
order by `p`.`id` desc 
limit 28 offset 0

Am i missing something here that makes that query too slow?

I also thought to combine the 'products' table with 'product_prices' table in one table to not use joins.

I'm using laravel 5.3 and PHP 7.0

Edit

I've the following foreign keys;

Table: landing_page_products product_id landing_page_id

Table: products category_id brand_id

Table: product_prices product_id

2 Answers 2

2

You need to add indexes on the fields used in joins or in where conditions. You can run explain on your query to see what indexes are used and where there is no index.

In your case, it seems that indexes on landing_page_id and parent_id will help a lot

6
  • The problem here that the filtering query may be built based on 3 main types: 'landing_page", 'category_id' and brand_id. So should index them all?
    – Hasan Zohdy
    Dec 30, 2017 at 14:55
  • @HasanZohdy just try it out, there's nothing you can damage. Running EXPLAIN on your query will also help to find out which indices might be needed
    – Nico Haase
    Dec 30, 2017 at 15:10
  • I'm really not familiar with indexes, This is what i get when i run the EXPLAIN query i.imgur.com/4ykJ1Kp.png What does this tell ?
    – Hasan Zohdy
    Dec 30, 2017 at 15:40
  • @HasanZohdy that means that the query does not use any indexes. Try adding index on product_id in the product_prices table and see the result Dec 30, 2017 at 15:50
  • @MaximKrizhanovsky the 'product_id' in the 'product_prices' table has a 'foreign key'. Should i index it too?
    – Hasan Zohdy
    Dec 30, 2017 at 15:51
2

The problem seems not the join but the improper use of subselect and INn clause

You query could be refactored as

select p.id, pp.title, pp.price, pp.sale_price, pp.discount_percentage, pp.image, t.lowest_price, t.highest_price
from products as p
inner join ( 
  select id, min(sale_price) as lowest_price, max(sale_price) as highest_price
  from products
  group by id  
  ) t on t.id = p.id
  inner join ( 
    select pt.product_id 
    from landing_page_products as pt 
    where pt.landing_page_id =18 
  ) t2 on p.id = t2.product_id
left join product_prices as pp on p.id = pp.product_id   

once you removed the unuseful subquery and join clause you then you can evaluate a proper indexing of your tables

and index on table product_price column product_id

CREATE INDEX my_product_index ON product_price (product_id )

and landing_page

CREATE INDEX my_landing_index ON landing_page(landing_page__id )
7
  • There was just a mistake in the table name 'all_products' as should be 'products', anyway, i tried the following query select p.id, pp.title, pp.price, pp.sale_price, pp.discount_percentage, pp.image from products as p inner join ( select pt.product_id from landing_page_products as pt where pt.landing_page_id =18 ) t2 on p.id = t2.product_id left join product_prices as pp on p.id = pp.product_id LIMIT 28 But still getting records in 6-7 seconds
    – Hasan Zohdy
    Dec 30, 2017 at 15:09
  • then once you have removed the unuseful subquery and in clause add the inde.... 6-7 seconds ..?? how many row in tables involeved and how many seconds whit your query???
    – ScaisEdge
    Dec 30, 2017 at 15:13
  • I already wrote the full details about total records in each table, but the highest tables here are landing_page_products 22K, products 48K and product_prices 248K. And for removing the sub queries it is still also too long to retrieve only 28 record
    – Hasan Zohdy
    Dec 30, 2017 at 15:17
  • answer update with a brief suggestion
    – ScaisEdge
    Dec 30, 2017 at 15:21
  • In the landing_page_products, i've 'landing_page_id' and 'product_id' are foreign keys, and the same in the 'product_prices' i've the 'product_id' as a foreign key. Should i index These columns? And the query not working for the first inner join. One more question, is the inner join is faster than the "where in" clause?
    – Hasan Zohdy
    Dec 30, 2017 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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