Mysql – Query between two related tables

MySQLquery

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are FK to the first table.

I want to check if there are any users in user_profile which are not in user_friend table, neither in user_profile1_id, nor user_profile2_id.

What query should I use?

EDIT:

CREATE TABLE user_profile (
  --fields
)

CREATE TABLE user_friend (
  user_profile1_id INT,
  user_profile2_id INT
)

Best Answer

Use a LEFT JOIN and check for null values. Something like:

SELECT profile.* FROM user_profile profile
 LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
 LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
 WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL

Here's a link to the various joins

Answer to question 1

This should do it for you

UPDATE user_activity a
INNER JOIN user_profile b 
USING (user_profile_id)
SET a.user_profile_id_int = b.user_profile_id_int;

This will work in MySQL. If you are not used to that JOIN syntax, do this:

UPDATE user_activity a
INNER JOIN user_profile b 
ON a.user_profile_id = b.user_profile_id
SET a.user_profile_id_int = b.user_profile_id_int;

Both should work.

Answer to question 2

Your query, in theory, works. However, look at what it is doing:

A table scan on user_activity, an indexed lookup of the user_profile_id_int using the PRIMARY KEY of user_profile, and an in-place update of the current row in user_activity.

The query is hitting two tables and two primary keys, back and forth, on a per-row basis. All steps slow each other down. Thus, you get a longer running query.

CAVEAT

Adding a compound index on user_profile should speed things up:

ALTER TABLE user_profile ADD INDEX (user_profile_id,user_profile_id_int);

Mysql – Choosing between MyISAM & InnoDB – two servers with related tables

"MyISAM is faster than InnoDB" -- This is an old wives tale. It used to be somewhat correct; now it is somewhat incorrect.

SELECTing during UPDATEing -- MyISAM is usually fast enough so this is not an issue, but InnoDB will usually not block at all. Go with InnoDB.

Power failure -- InnoDB automatically recovers.

You are talking about two "servers" -- Do you mean two different computers? And two differen instances of mysqld? There is nothing to have them talk to each other. (OK there are Replication and Federation, but it did not sound like you are into those.)

If you really meant "database" or "table" on a single MySQL instance, then so state. Tables (even in different databases) can be JOINed for most operations.

Best Answer

Related Solutions

Mysql – Update Large Table

Answer to question 1

Answer to question 2

CAVEAT

Mysql – Choosing between MyISAM & InnoDB – two servers with related tables

Related Question