Help
Right now I use a simple Shop_Product::find_products call on my category page. In that function you query the DB to get the IDs and then do this for each one:
$result_array = array();
if (count($product_ids))
{
foreach ($product_ids as $id)
$result_array[] = Shop_Product::create()->find($id);
$result = new Db_DataCollection($result_array);
}
else
$result = new Db_DataCollection(array());
Since we already know all the ids that we will be checking why not do something like Shop_Product::create()->find(array($ofIDs)) or Shop_Product::create->find_bulk($id);
The problem I have with the current implementation is that it results in 40+ queries that look like this:
[2011-04-19 19:46:05] SELECT (pages.url) as page_url, (0) as items_ordered, (if (shop_products.grouped = 1, concat(shop_products.name, " (", shop_products.grouped_option_desc, ")"), shop_products.name)) as grouped_name, (page_calculated_join.title) as page_calculated, (product_type_calculated_join.name) as product_type_calculated, (manufacturer_link_calculated_join.name) as manufacturer_link_calculated, (select group_concat(db_files.name ORDER BY 1 SEPARATOR ', ') from db_files where
db_files.master_object_id = shop_products.id and (master_object_class='Shop_Product' and field='images')) as images_calculated, (tax_class_calculated_join.name) as tax_class_calculated, (select group_concat(shop_categories.name ORDER BY 1 SEPARATOR ', ') from shop_categories, shop_products_categories where
shop_categories.id=shop_products_categories.shop_category_id and
shop_products_categories.shop_product_id=shop_products.id) as categories_calculated, (select group_concat(shop_products.grouped_option_desc ORDER BY 1 SEPARATOR ', ') from shop_products where
shop_products.product_id = shop_products.id) as grouped_products_all_calculated, (select group_concat(shop_custom_attributes.name ORDER BY 1 SEPARATOR ', ') from shop_custom_attributes where
shop_custom_attributes.product_id = shop_products.id) as options_calculated, (select group_concat(shop_extra_options.description ORDER BY 1 SEPARATOR ', ') from shop_extra_options where
shop_extra_options.product_id = shop_products.id and ((option_in_set is null or option_in_set=0))) as product_extra_options_calculated, (select group_concat(shop_extra_option_sets.name ORDER BY 1 SEPARATOR ', ') from shop_extra_option_sets, shop_products_extra_sets where
shop_extra_option_sets.id=shop_products_extra_sets.extra_option_set_id and
shop_products_extra_sets.extra_product_id=shop_products.id) as extra_option_sets_calculated, (select group_concat(shop_tier_prices.id ORDER BY 1 SEPARATOR ', ') from shop_tier_prices where
shop_tier_prices.product_id = shop_products.id) as price_tiers_calculated, (select group_concat(shop_products.name ORDER BY 1 SEPARATOR ', ') from shop_products, shop_related_products where
shop_products.id=shop_related_products.related_product_id and
shop_related_products.master_product_id=shop_products.id) as related_products_all_calculated, (select group_concat(shop_product_properties.name ORDER BY 1 SEPARATOR ', ') from shop_product_properties where
shop_product_properties.product_id = shop_products.id) as properties_calculated, (select group_concat(db_files.name ORDER BY 1 SEPARATOR ', ') from db_files where
db_files.master_object_id = shop_products.id and (master_object_class='Shop_Product' and field='files')) as files_calculated, (select group_concat(shop_customer_groups.name ORDER BY 1 SEPARATOR ', ') from shop_customer_groups, shop_products_customer_groups where
shop_customer_groups.id=shop_products_customer_groups.customer_group_id and
shop_products_customer_groups.shop_product_id=shop_products.id) as customer_groups_calculated, (select trim(concat(ifnull(lastName, ''), ' ', ifnull(concat(substring(firstName, 1, 1), '. '), ''), ifnull(concat(substring(middleName, 1, 1), '.'), ''))) from users where users.id=shop_products.updated_user_id) as updated_user_name, (trim(ifnull((select trim(concat(ifnull(lastName, ''), ' ', ifnull(concat(substring(firstName, 1, 1), '. '), ''), ifnull(concat(substring(middleName, 1, 1), '.'), ''))) from users where users.id=shop_products.created_user_id), ''))) as created_user_name, shop_products.* FROM shop_products LEFT JOIN pages ON shop_products.page_id=pages.id
LEFT JOIN pages as page_calculated_join ON page_calculated_join.id = shop_products.page_id
LEFT JOIN shop_product_types as product_type_calculated_join ON product_type_calculated_join.id = shop_products.product_type_id
LEFT JOIN shop_manufacturers as manufacturer_link_calculated_join ON manufacturer_link_calculated_join.id = shop_products.manufacturer_id
LEFT JOIN shop_tax_classes as tax_class_calculated_join ON tax_class_calculated_join.id = shop_products.tax_class_id
WHERE
(shop_products.id = '22465') LIMIT 0, 1
Ignoring the complex query just take a look at the end. Why not make it so that we can create objects in bulk if we already know the ids that results in a query that looks like this instead:
WHERE
(shop_products.id IN ('22465', '22465')) LIMIT 0, 1
That would eliminate 40 queries for me on each page.
Not to mention the same thing for images:
SELECT (concat(lastName, " ", firstName)) as user_name, (trim(ifnull((select trim(concat(ifnull(lastName, ''), ' ', ifnull(concat(substring(firstName, 1, 1), '. '), ''), ifnull(concat(substring(middleName, 1, 1), '.'), ''))) from users where users.id=db_files.created_user_id), ''))) as created_user_name, db_files.* FROM db_files LEFT JOIN users ON users.id=db_files.created_user_id WHERE (db_files.master_object_id = '21447') AND (db_files.master_object_class='Shop_Product' and field='images') ORDER BY sort_order, id
WHERE
(db_files.master_object_id IN ('21447', '21447'))
Would result in another 40+ query savings. Right now with a large catalog doing searching is taking me quite a bit of page load due to the number of queries needed to execute this really slows down my site.
Thanks!
