CODEWITHSUNDEEP
mysqldatabaseperformanceyiilarge-data
Rajeev R
Rajeev R

Reputation: 123

Querying large data in Yii?

I am using MySQL database for an application in Yii. The database contains both small (like storing user details) and large tables (storing large invoice data). I have join queries to fetch data from the large tables and that makes site very slow. So I enabled MySQL query cache to improve the performance. But that is not a permanent solution. After everyday, the data become very huge. Is there any suggestions to improve the performance? Is there any other db which can integrate with Yii to handle large data queries?

Here is the query,

SELECT u.accountnumber, u.chargedescription, ROUND(SUM(u.netamount), 2) as cost, p.value, p.price FROM `reports` `u` JOIN `reportsdata` `p` ON u.trackingnumber=p.value WHERE chargedescriptioncode='003' GROUP BY `u`.`trackingnumber`

"reports" and "reportsdata" both are large data tables. Both are joined with trackingnumber which is a huge list. So, whenever the trackingnumber become huge, the query lags and kills the cpu execution time.

Upvotes: 3

Views: 2001

Answers (1)

CreatoR
CreatoR

Reputation: 1652

For working with large data better use DAO and Query Builder instead ActiveRecord: http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

Less flexible, but more performance.

You can aggregate data and put it on the cache (not caching ActiveRecord, but caching prepared data)

Upvotes: 2

Related Questions