Using UNIQUE indices with NULL fields in MySQL?

I can check, periodically, for a list of users that are currently online. I want to turn this into something useful like a list of entries per user with login / logout time. There is no other way to determine this information apart from checking who is currently online.

After some thinking I came up with something like this:

CREATE TABLE onlineActivity (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR (32) NOT NULL,
  开发者_运维技巧  login_time DATETIME NOT NULL,
    logout_time DATETIME NOT NULL,
    time SMALLINT (3) NOT NULL DEFAULT 0,
    online BOOL DEFAULT NULL,
    UNIQUE (name, online),
    PRIMARY KEY (id)
)   ENGINE = MyISAM;

I run this query every few minutes to add/update names in the activity list:

INSERT INTO onlineActivity (name, login_time, logout_time, online)
    SELECT name, now(), now(), true FROM onlineList ON DUPLICATE KEY UPDATE logout_time = now()

And this query is run for every user that has logged out:

(the names are determined by comparing two adjacent online lists, the current one and the previous one)

UPDATE onlineActivity SET online = NULL WHERE name = ? AND online = 1

---

The questions:

• I'm worrying that using a NULL field (online) in a UNIQUE index is a bad idea, and will hurt performance. I figure that MySQL might have to do a full scan of all the online's (instead of using an index) for each name to find one that is not NULL. Could someone clarify if that is the case here? I couldn't find any information on how MySQL deals with this sort of situation.
• Do other database systems (PostgreSQL, SQLite) behave differently then MySQL in this regard?
• should I instead of the first query, run two queries for each name, to see if a specified user is currently online, and act accordingly on that?
• I thought of this design because I wanted to minimize the amount of queries used, is this a flawed idea in itself?
• This table will be getting around 300~500k new records per day. Is there something else I can do to lessen the performance decrease?

I want to store a full history of user activity, not a single entry.

---

I am not sure why you have a unique on name and online since what you are trying to do is create a list of online activity. Putting a unique key as you have specified will mean that you can only have a name in there three times, one for each state (null, true, false).

What you are effectively doing is trying to create a history table in which case to use your current method of populating the table you should put a unique key on (name, logout_time) with a null logout_time indicating a currently logged in user (since you would only want one logout time that is null).

Something like this:

CREATE TABLE onlineActivity (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR (32) NOT NULL,
    login_time DATETIME NOT NULL,
    logout_time DATETIME NULL,
    time SMALLINT (3) NOT NULL DEFAULT 0,
    online BOOL not null DEFAULT false,
    UNIQUE (name, logout_time),
    PRIMARY KEY (id)
)   ENGINE = MyISAM;

Then run this on a schedule to update the table

INSERT IGNORE INTO onlineActivity (name, login_time, logout_time, online)
    SELECT name, now(), null, true FROM onlineList

And this on user logout

UPDATE onlineActivity SET online = false, logout_time = now() WHERE name = ? AND logout_time = null