Suggestions needed: Pods, Large data, SQL & Objects
hello, first of great effort on PODS as of yet, i see 2.0 shaping up really well, so thinking how we'd be moving to 2.0 eventually, although we have been having a few issues with 1.9.4 with regards to pods processing large data
An example to highlight the current state of our implementation:
- Deal Pod consisting of 3,428 items as of now, growing at 100 a day or so
- Brand Pod consisting of 674 items as of now, growing at 20 a day or so
- City Pod consisting of 16 items as of now
as of now if i write a FindRecords query with 3 pick fields in it, the MySQL server timesout with an error "MySQL Server has gone Away or Query Execution was interuptted"
$deals_pod = new Pod('deal');
$where = "t.camp_end >= ".fix_date()." && t.daily=1 && city.name='".ucwords($city)."' && brand.id !='528'";
$deals_pod->findRecords('id DESC', 30, $where);
so the above Find Records is against the Deal Pod using Brand as a pick field & city as a Pick Field, since pod items are ever increasing i see this error happening
Tentative Resolution Thoughts:
- I see Pods moving to Wordpress's Object based system, would that help in a scenario where we could do just one SQL query load data in an Object n reuse this object everywhere further?
- Is there a possibility to use MySql Views which could be refreshed every few minutes?
- We would be offloading the unnecessary pod data to other tables from time to time, thus keeping the weight of the Pod against which findRecords has to work light
Offcourse we're using a Shared Server, with a Memory Limit of 64MB, due to which Admin Area also sometimes doesnt load data in Pick Fields as it loads the entire Pod Data in them, ive manage to mitigate that using Input helpers just to show the Selected fields as such, the main point is figuring out how to optimize & understand the way Pods will be tackling these things, would really appreciate any pointers!
Thank You!
1 Answer
A few things you can do to speed up your site, is to not have PICK fields, or rather -- to have single line text fields that store an ID (or for multiple IDs, separate by comma), then work with IDs in your queries. This avoids extra table joins and can increase speed on some of the slowest complex sites that I've seen. PICK fields are there to help you, but at a certain point they actually weigh you down more because of either having too many PICK fields or using too many of them in lookups where the related object has a large number of items itself.
Pods uses it's own brand of caching in 1.9.4 which means any query you make actually goes through a check to see if it's been made before. In 2.0 we're going to be utilizing a ton of other performance boosting methods like Object Caching, DB Caching (using wpdb), and Transients.
You can always use MySQL views, just pop in the $sql variable within findRecords or do findRecords(array('sql'=>$my_view_query)); to run a view that's saved on your MySQL server already.
Using a shared server has it's financial advantages, but the resource limits and variable performance can be a real downer. As you grow this site, consider upping your investment in hosting for better performance and reliability.
