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
-~----------~----~----~----~------~----~------~--~---
