Mysql version: 4.1.1
Platform: Linux, pre-compiled RPMs from mysql.com
Table 1:
CREATE TABLE `notifications` (
`recid` int(11) NOT NULL auto_increment,
`recdate` datetime NOT NULL default '0000-00-00 00:00:00',
`expiry` datetime default NULL,
`notify_title` varchar(150) default NULL,
`notify_body` text,
PRIMARY KEY (`recid`),
KEY `idx_recdate` (`recdate`)
) TYPE=MyISAM DEFAULT CHARSET=latin1
Table 2:
CREATE TABLE `notifications_seen` (
`notifid` int(11) NOT NULL default '0',
`notif_loginid` int(11) NOT NULL default '0',
`seenon` datetime default NULL
) TYPE=MyISAM DEFAULT CHARSET=latin1
Table 1 contains a list of notifications that exist.
Table 2 contains mappings of which notification IDs have been seen by which
loginids. (If you're a lloyds TSB user, you'll know what I'm aiming at.)
My query string:
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=$loginid);
My problem:
Right now, I use a routine that selects the IDs that haven't been seen, and
promptly does an insert into notifications_seen to flag that it has been
seen. This works fine. The moment I do alter table notifications_seen add
unique unq_notifid_loginid (notifid, notif_loginid), my query starts
returning utter foolishness - basically, no records where there were records.
Why does adding a unique index cause this? (Or have I just found a bug?)
Stage 1: No unique index:
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
+-------+---------------------+
| recid | notify_title |
+-------+---------------------+
| 3 | Some title here |
+-------+---------------------+
explain SELECT recid, notify_title FROM notifications WHERE recid NOT IN
(SELECT notifid FROM notifications_seen WHERE notif_loginid=2)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: notifications
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: notifications_seen
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30
Extra: Using where
Stage 2: Add unique .
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
Empty set (0.00 sec)
explain SELECT recid, notify_title FROM notifications WHERE recid NOT IN
(SELECT notifid FROM notifications_seen WHERE notif_loginid=2);
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: notifications
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: notifications_seen
type: index_subquery
possible_keys: unq_notif_login
key: unq_notif_login
key_len: 8
ref: func,const
rows: 2
Extra: Using index; Using where
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
