How to improve Magento queries using product_index

I had to write some queries to show specific products on my homepage. However, those are abnormally long and need to be cached.

This is an example of query done from my homepage:

$bogof = Mage::getModel('catalog/product')
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('status',1)
    ->addAttributeToFilter('promocode',10)
    ->addAttributeToFilter('visibility',array('neq' => 1));
$bogof->getSelect()->order('rand()');
$bogof = $bogof->getFirstItem();

As you can see, I need those to get a random product which takes a long time.

Basically this query fetches the information from magento_catalog_product_entity, magento_catalog_product_entity_int and magento_catalog_product_entity_varchar through 6 joins.

What I noticed it that on a category page, the information are fetched by Magento from magento_catalog_product_entity, magento_catalog_category_product_index, magento_catalog_product_index_price and magento_cataloginventory_stock_item which is a lot faster than joining 6 times

How c开发者_如何学Pythonan I use the same type of queries to get fetch my products? I tried to use the ResourceModel without any success.

Thanks in advance.

(Please forgive my english)

---

Product indexes will not improve performance.

Some advices to improve performance: First of all, do not select all attributes. Get only attributes that you really need (it's about addAttributeToSelect('*')) Next. Looks like you need the addAttributeToFilter method in the 2nd, 3rd, 4th and 5th lines. It will decrease the collection size and the collection will take less memory. Use cache and lazy-loading.