I have a view which lists the results of a number of tests. My (relevant) relations are:
Test HAS_ONE TestTemplate
Test HAS_MANY TestItems
TestItem HAS_ONE TestItemTemplate (contains the ‘type’ attribute that I care about)
TestItem BELONGS_TO Test
Initially I had a function to grab the count of passed, failed, untested, etc (6-7 possible results) items for each test, by iterating over every test item for each test on the page in a foreach loop (slow). It began taking > 5 seconds to load the page so I started looking for another way.
I added STAT relations to Test’s model on TestItem with condition result=TestItem::RESULT_FAILED, for example, so that I could just get an integer straight from MySQL without doing any iteration of my own. This shaved off a couple seconds, I think, but the load time is still longer than I’d like.
Next I tried doing TestItem::model()->count() which is about as fast as the STAT approach as far as I can tell. Is there a faster way in Yii implementation or PHP/MySQL in general to get this kind of count?
TL;DR: What’s the fastest way to count MySQL rows/records conditionally, with or without relations?