How to index and update a user comparison system?

I imagine this to be a social networking type, right? I can not see how feasible algorithms that compare users one by one. It would be very inefficient to run this in a trigger and "time-consuming" in a background process (how many minutes should the user wait to see the result?).

I believe that the ideal solution is a type of pre-classification that, applied to a given user, returns a value that can then be compared to other users.

I'll try to illustrate this.

First, let's take height . Imagine that we want to approach people with similar heights. We can set ranges of heights, for example, range 1 for people considered "low", 2 for "medians" and 3 for "highs."

For eye color , we could have the 1 value for light eyes, 2 for brown and 3 for dark.

For civil status , smoke , bebo and some other attributes that can have only two states, % and 1 .

After this classification, one can think of an algorithm that, from a set of interests, returns the most appropriate profiles.

The simplest could be a query that compares each attribute and returns first those with more similarities.

The query example below sorts the profiles by similarity, whose value is computed by adding 2 to each common attribute, that is, the more common attributes, the greater the column value:

select c.*,
( 
  case when faixa_altura = :faixa_altura_interesse then 1 else 0 end +
  case when tipos_olhos = :tipo_olhos_interesse then 1 else 0 end +
  case when bebo = :bebo_interesse then 1 else 0 end +
  (...)
) semelhanca
from caracteristicas c
order by semelhanca desc

Another numerical way to do this (which I found well explained in this SOEN question ) is to consider all these features as multidimensional axes as in a Cartesian graph. Then you will find the similarity between interests and profiles through the "location" of the profile.

Consider the image below ( source ):

Thecharacteristicsofeachprofilearerepresentedbyaperiod,right?Sotofindsimilarinterests,simplyretrievetheclosestpointsofinterest.

Thedifferenceisthatinyourcasethegraphwouldhave1dimensions,beingNthenumberofattributes.

Anotherfactortoconsiderisplacingweightsinthesecharacteristics.Forexample,drinkingornotcanbemoreimportanttomatchprofilesthanheight.Todothisintheabovequery,simplyuse,insteadofN,ahighervalueaccordingtotheimportanceofthecharacteristic.

Goingalittledeeper,ifgreateroptimizationisrequiredandaqueryisnotfeasible,youcanestablishpeopleprofiles.Whenausercompleteshischaracteristics,thesystemclassifieshimintooneoftheregisteredtypes.Thisisjustanotherkindofabstractiontosimplifythedatastructure.Themoreprofilesthereare,themorerefinedtheresultwillbe.Thisisthe"thickest" yet most efficient method. The weakness is that if someone does not fit well into a pre-set profile, the chances of not finding anyone compatible will be greater.

For each n users we will have n-1 comparisons, I did not see the "sex" in the tables, an application of this type seems important to me.

I would do a table in which I would record new users or have had some data registered, a batch routine (1) (triggered by event) would compare against n-1 users and record the Compared x User since what is true for user x > > user y is not true for user and > > user x. The SQL itself seems simple to me: attribute = attribute or attribute between attribute range the question would be to "populate" the relationship tables.

(1) Actually two procedures:

1 Read row users table to process   Flame writes attributes passing user as parameter   Deletes user from table to process

2 Calculate compatibility pair for each other user in the database   Record pair

The ideal for your case is to use a Trigger .

Responding to your comment, a Stored Procedure is a set of stored SQL statements that can be executed through a simple call. They can be given parameters and have some dynamic behavior.

More or less on the same line, a Trigger is also a set of stored statements. The difference between it and Stored Procedures is that Trigger is called automatically, because it is tied to some database operation involving a table (insertion of data into a table, for example).

The example below will make it clearer what I mean:

DELIMITER $$
CREATE TRIGGER antesDeUpdate_caracteristica 
BEFORE UPDATE ON caracteristica
FOR EACH ROW BEGIN
UPDATE compatibilidade
SET compatibilidade = -- Coloque o valor final aqui
WHERE user_id = OLD.user_id; END$$
DELIMITER ;

This Trigger guarantees that with each update in the caracteristica table, the compatibilidade table will be updated.

I do not know how your calculation is, but if you put the details of the compatibility calculation into the question, I can improve the answer.