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