Re: Problem with Large Datasets

[Femi Taiwo]

Yep. a custom query is seemingly the way forward on this. I use containable a lot for other areas. Pagination is not applicable in this scenario because I'm not displaying the results to anybody :) The resultant user ids are saved in another table for a cron job to notify the users. Giving the custom query a try right now. Thanks! Femi Martin Westin wrote: If you want to stay in Cake-land as much as possible try making use of the joins key in your options for find(). Nate wrote a nice article showing how to get going: http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find General advice (apart from your big search function) is: - Use Containable everywhere :) - Paginate any of these big datasets. Well, any dataset with a reasonable expectation of ever returning more than 25 records. So that would be most models. When you are doing that tricky joining query using the paginator might be tricky byt don't e lazy. Make your own paginator for that action. You can probably skip the ordering magic from paginator but simple paging of the results can be done quickly the bad old way. - Depending on the data you might get good results caching it. I usually don't but that is the fault of the type of data I most often deal with. If you can get away with the results sometimes being a few minutes "out of date" then caching could be for you. - I have also done my own per-request caching when the same data is likely to be found multiple times (yes, special cases). I got a huge leap in speed compared to the built-in query caching byt doing a special trick for my special situation. I use a combination of these techniques to crawl through datasets that are 50 times bigger (and more) than your 20'000 and still going like a rocket. But I am no genius. I was in your shoes last year when we went from 50 to 400'000 unique records in 3 days... man, was that a fun summer? :) I learned a lot by transferring the live data to my dev- setup and (very quickly) optimizing sloppy queries and limiting results the right way and so on. Keep asking specific questions if you have them. I'd be happy to help. If for nothing else...at least we can help show that Cake is not the slow-coach among php frameworks. /Martin On Jun 16, 12:42 pm, "Adam Royle" <a...@sleekgeek.com.au> wrote: It's possible to use a combination of cake's magic and raw sql together, which I use occasionally when I need something a bit out of the ordinary but still want to use the cake methods (to make use of model binding, etc). eg. function getHomepageArtists() {     $query = array(         'fields' => array('id', 'name', 'slug'),         'conditions' => 'Artist.id IN (SELECT artists_events.artist_id FROM artists_images INNER JOIN artists_events ON artists_events.artist_id = artists_images.artist_id)',         'contain' => array('Image'),         'order' => 'popularity, RAND()',     );         return $this->find('all', $query); } Hope that helps, Adam ----- Original Message -----   From: Femi Taiwo   To: cake-php@googlegroups.com   Sent: Tuesday, June 16, 2009 8:32 PM   Subject: Re: Problem with Large Datasets   Thanks for replying.   Actually, I've got everything properly indexed and closely followed standards on that. I honestly just wanted to avoid writing a custom query (lazy me). Was only hoping that there was a way to avoid custom queries. Will give that a try then.   Thanks!   Femi   Adam Royle wrote:     Hi Femi,     You should really just use one query for that with a number of joins and let the database do all the work. My advice is to get someone familiar with sql and construct the raw query and get it running at a decent speed inside the mysql console, and then find a way to get that sql into cake.     I'm guessing you don't have any indexes on your tables either, so best to get that same person to look over them. 20,000 records is not a lot and mysql can handle that easily if you know what you are doing.     Best of luck!     Cheers,     Adam     ----- Original Message -----       From: Femi Taiwo       To: Cake PHP       Sent: Tuesday, June 16, 2009 7:05 PM       Subject: Problem with Large Datasets       Hi all,       I'm having a scalability problem, and urgently need to refactor. The project is just 2 months old and already has over 20,000 users.       How do you handle passing an array with 5000+ values as a condition       e.g. $conditions = array('User.id'=>$bigArray);       As the records grow bigger, it takes longer.       I'm not looking to paginate because what happens is that an alert is sent to the users in the final result set. Browsing through the result set is NOT necessary.       Models?       I have a User model  with the following key relationships           hasMany UserDegree           hasMany Certification           hasMany Skill           hasMany Language           belongsTo City       The User model has a few columns as well - dob, marital status  and gender.       Herein lies the problem - a user can set different criteria for searching through the entire user database using any of the attributes above.       She can say for example, I want users in Kansas, aged between 22 and 27, who have degrees in either Computer Science, Mathematics or Physics, has Marketing skills       and can speak a bit of  French.       What I do right now is select from the users table, those aged between 22 and 27, then amongst these ones, those with the relevant degrees => then get from this set, the ones that have marketing skills. So I keep filtering using their user_ids. and passing them to the relevant model using conditions like $conditions = array('UserDegree.user_id'=>$bigArray);       Initially, this worked well with a few thousand records. But now with 20,000. Takes  3-5 seconds and is really cpu intensive and hard on the MySQL server.       What can I do?       Thanks!!       Femi --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---