Retrieve Magento Products with Attribute Value

Magento uses EAV Model to retrieve products, so you'll need to add additional attributes that you want to return.

$collection = Mage::getModel('catalog/product')->getCollection();
//fetch name and orig_price into data
$collection->addAttributeToSelect('name');
$collection->addAttributeToSelect('orig_price');

The following shows how to filter by a range of values (greater than AND less than):

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('name');
$collection->addAttributeToSelect('orig_price');
//filter for products whose orig_price is greater than (gt) 50
$collection->addFieldToFilter(array(array('attribute'=>'orig_price','gt'=>'50'),));
//AND filter for products whose orig_price is less than (lt) 100
$collection->addFieldToFilter(array(array('attribute'=>'orig_price','lt'=>'100'),));

While this will filter by a name that equals one thing OR another.

$collection = Mage::getModel('catalog/product')->getCollection();
//filter for products who name is equal (eq) to Widget A, or equal (eq) to Widget B
$collection->addFieldToFilter(array(array('attribute'=>'name','eq'=>'Widget A'), array('attribute'=>'name','eq'=>'Widget B'),));

A full list of the supported short conditionals (eq,lt, etc.) can be found in the _getConditionSql method in lib/Varien/Data/Collection/Db.php

Finally, all Magento collections may be iterated over (the base collection class implements on of the the iterator interfaces). This is how you'll grab your products once filters are set.

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('name');
$collection->addAttributeToSelect('orig_price');
//filter for products who name is equal (eq) to Widget A, or equal (eq) to Widget B
$collection>addFieldToFilter(array(array('name'=>'orig_price','eq'=>'Widget A'),array('name'=>'orig_price','eq'=>'Widget B'),));
foreach ($collection as $product) {
var_dump($product->getData());
}

Solution by Alan Storm

Why is Magento so slow?

These are some of the reasons behind the slowness of magento:

1. Parts of Magento use an EAV database system implemented on top of MySQL. This means querying for a single "thing" often means querying multiple rows

2. There's a lot of things behind the scenes (application configuration, system config, layout config, etc.) that involve building up giant XML trees in memory and then "querying" those same trees for information. This takes both memory (storing the trees) and CPU (parsing the trees). Some of these (especially the layout tree) are huge. Also, unless caching is on, these tree are built up from files on disk and on each request.

3. Magento uses its configuration system to allow you to override classes. This is a powerful feature, but it means anytime a model, helper, or controller is instantiated, extra PHP instructions need to run to determine if an original class file or an override class files is needed. This adds up.

4. Besides the layout system, Magento's template system involves a lot of recursive rendering. This adds up.

In general, the Magento Engineers were tasked, first and foremost, with building the most flexible, customizable system possible, and worry about performance latter.

The first thing you'll want to do to ensure better performance is turn caching on (System -> Cache Management). This will relive some of the CPU/disk blocking that goes on while Magento is building up its various XML trees.

The second thing you'll want to do is ensure your host and/or operations team has experience performance tuning Magento.

This was the answer by Alan Storm.