How to write better this Mysql Join query

Question

I have the next tables

Users {id, name}
Messages {id, user_id, cache_user_name}

What I want is to do a JOIN only when cache_user_name is NULL for performance reasons.

For example:

SELECT Messages.*, Users.name FROM Messages INNER JOIN Users ON (Messages.user_id = Users.id) 
// ON (ISNULL(Messages.cache_user_name) AND ...

The best way is doing 2 queries? 1 for rows without cache (join) and the other for cached rows with a join?

[EDIT]

The result I need is:

Users

ID: 1, NAME: Wiliam

Messages

ID: 1, USER_ID: 1, CACHE_USER_NAME: Wiliam
ID: 2, USER_ID: 1, CACHE_USER_NAME: null

Result

ID: 1,  USER_ID: 1,  CACHE_USER_NAME: Wiliam,  USERS.NAME: null   // No join, faster
ID: 2,  USER_ID: 1,  CACHE_USER_NAME: null,    USERS.NAME: Wiliam // Join

Answer 1

You can add WHERE ... IS NULLclause.

The optimizer will (try to) use the best performing plan.

SELECT   Messages.*
         , Users.name 
FROM     Messages 
         INNER JOIN Users ON (Messages.user_id = User.id)
WHERE    Users.cache_user_name IS NULL

Edit

Given following data, what would you expect as output?

DECLARE @Users TABLE (ID INTEGER, Name VARCHAR(32))
DECLARE @Messages TABLE (ID INTEGER, User_ID INTEGER, Cache_User_Name VARCHAR(32))

INSERT INTO @Users VALUES (1, 'Wiliam')
INSERT INTO @Users VALUES (2, 'Lieven')
INSERT INTO @Users VALUES (3, 'Alexander')

INSERT INTO @Messages VALUES (1, 1, NULL)
INSERT INTO @Messages VALUES (2, 1, 'Cached Wiliam')
INSERT INTO @Messages VALUES (3, 2, NULL)
INSERT INTO @Messages VALUES (4, 3, 'Cached Alexander')

SELECT  *
FROM    @Users u
        INNER JOIN @Messages m ON m.User_ID = u.ID
WHERE   m.Cache_User_name IS NULL        

Answer 2

SELECT m.Id, m.user_id, CACHE_USER_NAME user_name
FROM messages m
WHERE CACHE_USER_NAME IS NOT NULL
UNION ALL
SELECT m.Id, m.user_id, u.user_name user_name
FROM (Select * from messages Where cache_user_name IS NULL) m
JOIN users ON (u.user_id = m.user_id)

Anyway best approach store cache_user_name in table message during creating message. Then you will need join at all.

Answer 3

I think those joins in previous answers with a Not Null where clause should work fine, but maybe we're not following your in-efficiencies problem. As long as users.id and messages.user_id are indexed and of the same type, that join shouldn't be slow unless you have a huge user database and lots of messages. Throw more hardware at it if it is; likely you are running a lot of traffic and can afford it. :)

Alternatively, you could handle it like this: do a query on Messages where the name isn't null, run through the results, find the names for each message (and put them in an array), then query the User's table for just those names. Then as you loop over the Messages results you can display the proper name from the array you saved. You'll have two queries, but they'll be fast.

$users = $messages = $users_ids = array ();

$r = mysql_query('select * from Messages where cache_user_name is not null');
while ( $rs = mysql_fetch_array($r, MYSQL_ASSOC) )
{
    $user_ids[]    = $rs['user_id'];
    $messages[] = $rs;
}

$user_ids = implode ( ',', $user_ids );
$u = mysql_query("select * from Users where id in ($users)");

while ( $rs = mysql_fetch_array($r, MYSQL_ASSOC) )
{
    $users[$rs['id']] = $rs['name'];
}


foreach ( $messages as $message )
{
    echo "message {$message['id']} authored by " . $users[$message['user_id']] . "<br />\n";
}