-
- atang
 -
Total Posts: 46
Joined: 2008-07-10
|
I also tried the “is_new” attribute suggested by calinT in this thread (http://www.magentocommerce.com/boards/viewthread/8020/#t34861). At first it didn’t work, but it was because I missed all the changes. Make sure you get them all, and try refreshing your cache if that’s enabled.
In any case, it didn’t really make a difference for me. Initial load times (i.e. after not accessing the storefront for a while) are still extremely slow, and my host is reporting the query is not efficient. It doesn’t look too different from when it joined with the table that gave the new from/to dates:
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 66
SELECT `e`.*, IFNULL(_table_is_new.value, _table_is_new_default.value) AS `is_new`, IFNULL(_table_name.value, _table_name_default.value) AS `name`, `_table_price`.`value` AS `price`, IFNULL(_table_small_image.value, _table_small_image_default.value) AS `small_image`, `_table_special_price`.`value` AS `special_price`, IFNULL(_table_special_from_date.value, _table_special_from_date_default.value) AS `special_from_date`, IFNULL(_table_special_to_date.value, _table_special_to_date_default.value) AS `special_to_date`, IFNULL(_table_status.value, _table_status_default.value) AS `status`, IFNULL(_table_visibility.value, _table_visibility_default.value) AS `visibility` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_int` AS `_table_is_new_default` ON (_table_is_new_default.entity_id = e.entity_id) AND (_table_is_new_default.attribute_id=’579’) AND _table_is_new_default.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `_table_is_new` ON (_table_is_new.entity_id = e.entity_id) AND (_table_is_new.attribute_id=’579’) AND (_table_is_new.store_id=’1’)
INNER JOIN `catalog_product_entity_varchar` AS `_table_name_default` ON (_table_name_default.entity_id = e.entity_id) AND (_table_name_default.attribute_id=’45’) AND _table_name_default.store_id=0
LEFT JOIN `catalog_product_entity_varchar` AS `_table_name` ON (_table_name.entity_id = e.entity_id) AND (_table_name.attribute_id=’45’) AND (_table_name.store_id=’1’)
INNER JOIN `catalog_product_entity_decimal` AS `_table_price` ON (_table_price.entity_id = e.entity_id) AND (_table_price.attribute_id=’49’) AND (_table_price.store_id=0)
INNER JOIN `catalog_product_entity_varchar` AS `_table_small_image_default` ON (_table_small_image_default.entity_id = e.entity_id) AND (_table_small_image_default.attribute_id=’60’) AND _table_small_image_default.store_id=0
LEFT JOIN `catalog_product_entity_varchar` AS `_table_small_image` ON (_table_small_image.entity_id = e.entity_id) AND (_table_small_image.attribute_id=’60’) AND (_table_small_image.store_id=’1’)
LEFT JOIN `catalog_product_entity_decimal` AS `_table_special_price` ON (_table_special_price.entity_id = e.entity_id) AND (_table_special_price.attribute_id=’50’) AND (_table_special_price.store_id=0)
LEFT JOIN `catalog_product_entity_datetime` AS `_table_special_from_date_default` ON (_table_special_from_date_default.entity_id = e.entity_id) AND (_table_special_from_date_default.attribute_id=’51’) AND _table_special_from_date_default.store_id=0
LEFT JOIN `catalog_product_entity_datetime` AS `_table_special_from_date` ON (_table_special_from_date.entity_id = e.entity_id) AND (_table_special_from_date.attribute_id=’51’) AND (_table_special_from_date.store_id=’1’)
LEFT JOIN `catalog_product_entity_datetime` AS `_table_special_to_date_default` ON (_table_special_to_date_default.entity_id = e.entity_id) AND (_table_special_to_date_default.attribute_id=’52’) AND _table_special_to_date_default.store_id=0
LEFT JOIN `catalog_product_entity_datetime` AS `_table_special_to_date` ON (_table_special_to_date.entity_id = e.entity_id) AND (_table_special_to_date.attribute_id=’52’) AND (_table_special_to_date.store_id=’1’)
INNER JOIN `catalog_product_entity_int` AS `_table_status_default` ON (_table_status_default.entity_id = e.entity_id) AND (_table_status_default.attribute_id=’69’) AND _table_status_default.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `_table_status` ON (_table_status.entity_id = e.entity_id) AND (_table_status.attribute_id=’69’) AND (_table_status.store_id=’1’)
INNER JOIN `catalog_product_entity_int` AS `_table_visibility_default` ON (_table_visibility_default.entity_id = e.entity_id) AND (_table_visibility_default.attribute_id=’74’) AND _table_visibility_default.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `_table_visibility` ON (_table_visibility.entity_id = e.entity_id) AND (_table_visibility.attribute_id=’74’) AND (_table_visibility.store_id=’1’) WHERE (e.entity_type_id = ‘4’) AND (IFNULL(_table_is_new.value, _table_is_new_default.value) = ‘1’) AND (IFNULL(_table_status.value, _table_status_default.value) in (1)) AND (IFNULL(_table_visibility.value, _table_visibility_default.value) in (2, 4))
As far as other tips, I highly recommend setting up your .htaccess config to implement compression for both the php output and static files (other than images), and setting an expires header so that the user is able to pull images from cache (the caveat being if you make changes, the user may not see it, so watch how far in the future you set the “expires"). This won’t help so much with the “initial” load time, but speed up the user’s experience after that. If you use Firefox, download Firebug and Yslow (just search for them). The provide very useful information to help you debug your site and evaluate performance (from the client side).
I’m tempted to just create a static landing page, so that users landing at my store will get a response quickly on the initial load, although this will require managing it more aggressively to keep up with changes, and the user may be waiting on their next action
Any other ideas out there? Optimizations? Maybe there’s some index on the DB that needs to be defined. Any database experts out there? 
|
