Limiting Collection Size in Magento

Sometimes you need to get a collection of objects from Magento but only want to limit them to a certain number. This is especially useful if your collection is going to be in the hundreds of thousands – as you do not want to be performing complex operations on a collection of that size.

Some of the best ways to limit your collection size are:


This function will limit the number of results that are returned – basically using a LIMIT X mysql query. For example:

$collection = Mage::getModel('catalog/product')->getCollection();
echo $collection->count(); // will echo 10


setPage() is similar to using setPageSize but you can add two parameters – just like you would with mysql LIMIT 10,5 for example. The first parameter being the start from point – the next being how many you wish to bring back:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->setPage(0,20); // will bring back the first 20 products
$collection->setPage(10,5); // will bring back 5 products after the 10th product


This function allows you to add your own limit to the select query – its basically the same as entering LIMIT X. For example:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->limit(20); // will bring back our first 20 results

A handy hint to see what these limits are doing to your mysql query is to echo out your mysql query in the code. To do this you can simply perform the following after you’ve tweaked your $collection object:

echo $collection->load()->getSelect(); // will echo out our mysql query
  • Robiul

    It works! Thanks!

  • kapsi

    in Magento 1.8.1 you can use


    to echo Query

  • Bart

    The problem with ->setPage() is that even if you will set page to 100 and you haven’t got such as many products and expect no results it returns last page instead… Pretty weird

  • Indio John

    I am unable to fetch only minimum number of categories by using this getlimit function?