Re: Rating results after relevance. Difficult problem

This is more of a database design/normalization issue than a PHP-DB issue, but why not........ This is the "optimal" setup, IMHO, but you can use it as a framework for your own existing tables. This will allow a User to have any number of languages and any number of Interests. You COULD do it with Interest1, Interest2, etc columns but you'll go crazy in a hurry. It's better to limit that in the business logic/application code anyways, to allow for future growth. Psudo-code, not guaranteed to work but just convey the idea... LanguageDefinitions LanguageID integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY LanguageName VARCHAR(255) UserLanguageMaps UserID integer unsigned NOT NULL LanguageID integer unsigned NOT NULL INDEX (UserID, LanguageID) Users UserID integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY InterestDefinitions InterestID integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY InterestName VARCHAR(255) UserInterestMaps UserID integer unsigned NOT NULL InterestID integer unsigned NOT NULL INDEX (UserID, InterestID) SELECT UserID, count(LanguageDefinitions) as match_language_count, count(InterestDefinitions) as match_interest_count FROM Users, UserLanguageMaps, UserInterestMaps, LanguageDefinitions, InterestDefinitions WHERE UsersLanguageMaps.LanguageID IN (id,id,id,id,etc) AND Users.LanguageMaps.UserID = Users.UserID AND Users.UserID = UserInterestMaps.UserID AND UserInterestMaps IN (id,id,id,id,etc) AND LanguageDefinitions.LanguageID = UserLanguageMaps.LanguageID AND InterestDefinitions.InterstID = UserInterestMaps.InterestID; You may substitute JOIN calls if you expect zero matches in one or more tables. Party on. -Szii ----- Original Message ----- From: "andy" <news.letters@gmx.de> To: <php-db@lists.php.net>; <php-general@lists.php.net> Sent: Wednesday, May 08, 2002 11:50 AM Subject: [PHP-DB] Rating results after relevance. Difficult problem > Hi there: > > I am working on a member search engine. Regarding on the criterias a user > provides (like should speek english, should be interested in arts...) the > machine querries the db and returns the results. But I would like to rate > the results. So All criterias fitt... 100%, Just 1 of 3 -> 33 % and so on. > > I did already finish my coding and works fine for a user amount of 20. But > now I generated 2000 random users and woww!!! Take about 2 minitues to > querry!! > > So there has to be a better way, right? > > What I am doing with this version, is to get all the users fitting the > criteria. Opening a temporary table store the id and the amount of fitting > criterias in it. Retriving this temporary table and sort it after the most > wanted user (100% max). > > The problem with this, is that on user takes about 0.04 s to check and store > in the temp table. If there are 2000 users fitting at least one of the > criterias thats 2000 * 0.04 s means 400 s = lots of time! Not to imagine > what happens if there are 100.000 users in the db!!! > > So the querry performance is ok, I just need to find a more effective way to > rate them. There are 3 tables. > - user table with most of the data > - language table, containing the languages the user speak (by the time of > registering they can select up to 3 languages) > - interest table, containing the interests of the users (by the time of > registering they can select up to 20 interests) > > Can anybody help on that difficult topic? I would really apreciate any hint > on that. > > Thanx in advance, > > Andy > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php