MySQL most efficient method to find missing values from multiple tables -


I have three tables, the first is the list of email addresses: 

  Addresses: Id - integer, this is the primary key & lt; Br> Apparently to get into the address desk: - Integer, foreign key reference id: addresses - sent "and" received "tables are the other columns, but they can not Not important for the sent and received tables every time an email is sent or received and if the address is not already in the "Addresses" table, then it is added.T tables can be quite large (100,000 +) . <<< p> 
 "Sent" and " Entries for the "received" tables are purified on a regular basis and the entries to be removed due to various reasons, leaving the orphaned entries in the "address" table.  
 I clean orphaned entries in the "Addresses" table To find the most effective method in MySQL, I have a query that is yet to be done:  
  Delete from addresses where id is not (choose to get rid of) and no id (Choose the sender sent);  
 
 This works, but it can take a lot of time to run and certainly not the most effective way to do it! I also Tried to: 
 
  Remove from the address where it is not present (from where 's' was sent, select sid = addresses.id) and does not exist (Received from LS Select 'X' where get rid of = addresses.id); It was a little faster, but still takes a lot of time, I suspect that I need to use the join syntax, but at this time my SQL knowledge has taken part in me!   
 
 
 this should do the trick  
  deleted ads Send.sid = adresses.id LEFT received received received.Raid = adresses.id has been sent where SID is zero and received.It is

Comments

Post a Comment

Popular posts from this blog

Python SQLAlchemy：AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'schema' -

I tried to create a new database in my project, but when I run the script, I get this error, I have another project, this type of definition worked before, but now it gets a single error. I am using Python 2.7.8 and the version of SQLAlchemy module is 0.9.8. By the way, using a project flask- SQLAlchemy, it works well, I'm confused. Traceback information is as follows: Traceback (most recent call final): File "D: / Projects / IOM / db_create.py", line 4, & lt; Module & gt; From the models the base file "D: \ Projects \ OO-IM \ models.py", in line 15, in the & lt; Module & gt; Column ( 'followed_id', int (), ForeignKey ( 'user.id')) file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ schema.py", line 369, __new__ schema = metadata. Schema file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ elements.py", line 662, AttributeError in __getattr__ key): Neither 'column' object nor ...
Read more

java - How not to audit a join table and related entities using Hibernate Envers? -

I use Eners to be hibernate to audit my institutions. I have an audit entity, Foo , which includes a list & lt; Bars & gt; However, I do not want to audit the bar institutions, in the form of properties, I have written that: @inti @edicated public class Foo {@JoinTable (name = "T_FOO_BAR", joinColumns = @JoinColumn (name = "FOO_ID"), inverseJoinColumns = @JoinColumn (Name = "BAR_ID")) @ManyToMany (Cascade = Persist) @ Indited (targetAuditMode = RelationTargetAuditMode.NOT_AUDITED ) Public listing & lt; Bars & gt; GetBars () {Return bars; }} Now, I want to retrieve an amendment of foo : AuditReader reader = AuditReaderFactory.get (GetEntityManager ()); FU modification = (FU) reader.CreativeTeA () .entensEtrevision (Foo Class, 42) .getSingleResult (); Unfortunately, when I want to retrieve all the data (i.e. when it loads lazy times ), error me ORA-00942: table or view is not present , because Tried to query this: Sel...
Read more

mongodb - CakePHP paginator ignoring order, but only for certain values -

While pagging my user model in kppp, I can sort with some values, but not others. For example, I can order results from created or email , for example, but username or reputation seems to come back order by arbitrary command for example with a list of users: $ this-> paginate = array ('condition' => array ( 'User.is_active' = & gt; true), 'border' => 24, 'order' => array ('User.created' => DESC)); Works as expected, but $ this - & gt; Paged = array ('conditions' = & gt; array (' user.isIactive '= & gt; true),' border '= & gt; 24,' command '= & gt; array (' user repeats' = ' & Gt; DESC ')); No, no. I first thought that this could be a database issue, but when I search the database directly, sorted as expected. Note: I am using Mangodebi with the Mongo DB plugin of Ichikawa for the KPPHP; Pagnetizing users are used to work properl...
Read more