Mysql Takes too long retrieving records using joins

Question

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

Answer 1

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

Answer 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 )