database - Efficient checking of possible duplicate entities -


A user database needs to prepare a list of potential duplicates before saving an entity and possibly warn them Duplicate

There are 7 criteria on which we should check the duplicates and if at least 3 matches we should flag the user. All the matches will be on the criteria ID, so no fuzzy string matching is necessary, but my problem comes from the fact that there are several possible ways to match for at least 3 items (99 ways if I have done my timeline. 7) List of prospects.

I do not want to do 99 different DB queries to search my search results nor do I want to bring back many DBs and filter on client side. We are probably talking about a few thousand records at present, but it will grow in millions because the system gets mature.

Has anyone found a good efficient way to do this? I was considering a simple or query to get those records where at least one field matches DB, and then the customer is doing some processing to filter some more, but some fields have very little cardinalis And will not really be less

or And case will work but quite inefficient, because they do not use the index May include.

You need to create UNION to index.

If a user enters names , phone , email and addresses databases , And you want to see all those records that match at least 3 of these areas, you issue: 

  SELECT i. * FROM (SELECT id, COUNT (*) from (t_info T by selection ID) where name = 'Eve Chianese' union t_info T WHERE phone = '+15558000042' UniOnS All Select ID FD TIFO T WHERE email = ' 42 @ example.com 'Union All Select ID FDT_INFO T WHERE Address = '42 North Lane') ID (*)> = 3) dq on JOIN t_info i.id = dq if the ID Group has an ID. Id

This will use the index on these areas and the query will expire soon.

View this blog in my blog for details:

  • : At least 3 of 4 How to get a match-related record

    • Also see that the article is based on.

    If you want to create a list of DISTINCT values ​​in existing data, you wrap this query in a subquery: 

      SELECT i. * T_info i1 from where to select (Select from t_info t by selection ID where name = i1.name union selects all t_info T from ID where call = i1.phone union selects all t_info t where email = i1. Mail unius all select ID t_info T WHERE address = i1.address) q * (*)> = 3) ID that will be the ID by the group

    Note that this DISTINCT is not infected: if one matches b and b matches c , this does not mean that < Code> a match c .


Comments

Post a Comment

Popular posts from this blog

python - Overriding the save method in Django ModelForm -

I'm having trouble overriding a ModelForm save method. I get this error I'm: Exception type: TypeErier Exception Value: Save () found an unexpected keyword argument 'committed' My intentions include a form 3 fields Submit several values ​​for, then create an object for each combination of those areas, and to save each object file models.py class collarsult type (models.Model): id = model. AutoField (db_column = 'icontact_result_code_type_id', primary_key = true) callResult = models.ForeignKey ( 'CallResult', db_column = 'icontact_result_code_id') campaign = models.ForeignKey ( 'campaign', db_column = 'icampaign_id') CALLTYPE = models.ForeignKey ( 'CALLTYPE', db_column = 'icall_type_id') agent = models.BooleanField (db_column = 'bagent', default = true) teamLeader = models.BooleanField (db_column = 'bTeamLeader', default = true) active = models.BooleanField (db_column = Django.form...
Read more

html - CSS autoheight, but fit content to height of div -

I have a div in which there are three children- div in the left and middle div: float: left while true: float: true because it is my layout Actually will spoil, I've used ClearFix Hack: .cf {zoom: 1; }. Cf: First,. Cf: {content: ""; Display: Table; }. Cf: After {clear: both; } It still works, but I want to be the right div an indicator. So it should fill 100% of the parent unit of height. How can I complete this? PS is the full code: & Lt; / Div & gt; & Lt; Div class = "mobile_content" & gt; & Lt; H5 & gt; {{Data.titel}} & lt; / H5> & Lt; Table & gt; & Lt; TR & gt; & Lt; Th & gt; Komponist: & lt; / Th & gt; & Lt; TD & gt; {{Data.komponist}} & lt; / TD & gt; & Lt; / TR & gt; & Lt; TR & gt; & Lt; Th & gt; Instrument: & lt; / Th & gt; & Lt; TD & gt; {{Data.instrumente}} & lt; / TD & gt; & Lt; / TR & gt;...
Read more

qt - How to prevent QAudioInput from automatically boosting the master volume to 100%? -

I have been trying to use Qt5 multimedia to record audio with QAudioInput, however, when I see If QAudioInput is started, it increases the master volume of my sound device to 100%. How can I prevent QAudioInput from changing the master volume? My current development platform is Linux with PalsAdio (with flat audio disabled). How can I use QAudioInput : QAudioDeviceInfo device_info = QAudioDeviceInfo :: defaultInputDevice (); QAudio format format; Format.setSampleRate (44100); Format.setChannelCount (1); Format.setSampleSize (16); Format.setCodec ("Audio / PCM"); Format.setSampleType (QAudioFormat :: SignedInt); Format.setByteOrder (QAudioFormat :: LittleEndian); Std :: cout & lt; & Lt; Device_info.deviceName (). ToUtf8 (). ConstData () & lt; & Lt; Std :: endl; QAudioInput * default_device = new QAudioInput (device_info, format); QIODevice * default_io_device = default_device-> Start (); a QAudioInput.setVolume () method is not you I ...
Read more