The subquery limits results perfectly when the WHERE clause at the end of the statement is not included. I understand that the subquery LIMIT happens first then the WHERE clause is fired on that result set and that this is a limitation/restriction of a subquery.
What I need is someone more experienced than me to help a brother out with retrieving the records with a LIMIT with the ability to restrict that result set by the WHERE clauses. Let me know if this was not explained well enough.
I also scoured the interwebs in search of the answer for hours with no luck. Your time is appreciated.
EDIT: added a crude example on SQLfiddle: http://sqlfiddle.com/#!9/2de563/4
SELECT *
FROM (SELECT * FROM parent_products LIMIT 10) pp
INNER JOIN products_variants pv ON pv.parent_id=pp.parent_id
INNER JOIN products p ON p.id=pv.product_id
INNER JOIN product_types pt ON pt.product_type_id=p.product_type
LEFT JOIN team_list t ON pp.team_id=t.team_id
LEFT JOIN photos ph ON ph.product_id=p.id
LEFT JOIN product_attributes pa ON pa.product_id=pv.product_id
LEFT JOIN attributes a ON a.id=pa.attribute_id
LEFT JOIN product_attribute_options po ON po.product_attribute_option_id=a.parent_id
WHERE t.team_id=100 AND p.active='y';
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `attributes` ADD INDEX `attributes_idx_id` (`id`);
ALTER TABLE `photos` ADD INDEX `photos_idx_product_id` (`product_id`);
ALTER TABLE `product_attribute_options` ADD INDEX `product_options_idx_product_id` (`product_attribute_option_id`);
ALTER TABLE `product_attributes` ADD INDEX `product_attributes_idx_product_id` (`product_id`);
ALTER TABLE `product_types` ADD INDEX `product_types_idx_product_id` (`product_type_id`);
ALTER TABLE `products` ADD INDEX `products_idx_active` (`active`);
ALTER TABLE `products_variants` ADD INDEX `products_variants_idx_product_id` (`product_id`);
ALTER TABLE `team_list` ADD INDEX `team_list_idx_team_id` (`team_id`);
SELECT
*
FROM
(SELECT
*
FROM
parent_products LIMIT 10) pp
INNER JOIN
products_variants pv
ON pv.parent_id = pp.parent_id
INNER JOIN
products p
ON p.id = pv.product_id
INNER JOIN
product_types pt
ON pt.product_type_id = p.product_type
INNER JOIN
team_list t
ON pp.team_id = t.team_id
LEFT JOIN
photos ph
ON ph.product_id = p.id
LEFT JOIN
product_attributes pa
ON pa.product_id = pv.product_id
LEFT JOIN
attributes a
ON a.id = pa.attribute_id
LEFT JOIN
product_attribute_options po
ON po.product_attribute_option_id = a.parent_id
WHERE
t.team_id = 100
AND p.active = 'y'