Match and filtering values in members' db

[artworthy]

I want to filter and match values that members have checked boxed. There's over 60 values possible from 20 fields that will be changing quite often. There will also be a simple calculation once each match is found. What would be the most efficient way to approach this task? If membership scales up a lot parsing these matches could take quite a bit of time.

 

Thanks

Al

[Ken2k7]

Can you give me an example of what you want to accomplish?

[artworthy]

Member "A" has check boxed values 1,4,5,6,9,45. Member "B" has check boxed values 2, 5.9, 31,36,50, 57. They match on 5 and 9. Member A & B would be in the same group based on the values 5 and 9 match, followed by a calculation on the relationship of the 5 the 9 values.  (member matches could be as high as 20, though, and constantly changing)

 

What I thought might work best is creating a cron job to continually update an index that would filter out member id's not matching on any values -- thus at least cutting down on the number of member comparisons/calculation needed. Any suggestion on getting more efficiency?

 

 

[Ken2k7]

Well how are you storing them in the DB? I'm guessing you're storing them in the DB. If so, I believe a SQL should find the matching set of checked boxes between 2 members.

[artworthy]

Yes, all values would be stored in db in corresponding fields. Memory VARs of values from Member "A" would be sent server side to compare with all other members 20 fields. Match filtering not a problem. BUT... if membership scales up into the millions the parsing (with an add'l calculation for each match) of each member's set of values against Member "A" could really slow things down. Any thoughts re cron job crawler to indexer? Or better architecture choices in mind?

[Ken2k7]

I think MySQL's fast enough. It's not heavy querying.

 

I would have the table be:

id

user_id

value (just a number like you have it above)

 

Then have the SQL be

SELECT t1.value
FROM `table` t1
INNER JOIN `table` t2
    ON (t2.userid = '$member_two_id' AND t1.value = t2.value)
WHERE t1.userid = '$member_one_id';

 

SQL not tested.

[artworthy]

Thanks, I see where you're going and you think mysql could parse quickly enough in the millions of members range? The INNER JOIN would have to be for any of the 20 fields being a match. Then there would need to be a loop to do a calculation  and INSERT. You feel all this would process pretty quick scaled up?

[Ken2k7]

Probably have it be a CRON job that runs everyday at night time or whenever traffic is low and store the results in another table. You can monitor when that is, can't you?

[artworthy]

yes, timing not an issue for cron. So you think an indexer for this is the way to go?  I'd probably also want to have DATE () and TIME () inserted into "canvassing" table in order to skip over unchanged rows. Thanks for your help... any other suggestion hugely appreciated.

 

-Al

[Ken2k7]

May not be a bad idea to have an int field ENUM(1,0) to determine if that user has updated their selections. Then just have CRON pick those up and change them back to 0.

[arlyn9391]

I want to filter and match values that members have checked boxed. There's over 60 values possible from 20 fields that will be changing quite often. There will also be a simple calculation once each match is found. What would be the most efficient way to approach this task? If membership scales up a lot parsing these matches could take quite a bit of time.

 

Thanks

Al

 

That is a nice filter. There will also be a simple calculation once each match is found. But what would be the most efficient way to approach that task?

 

 

 

_________________

Aprilaire