Description:
Reported by Duncan Hill:
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
How to repeat:
Tables (dhill_notifications_for_reznichenko.tar) were uploaded to the ftp.mysql.com:/pub/mysql/Incoming directory.
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
ALTER TABLE notifications_seen ADD UNIQUE unq_notifid_loginid (notifid, notif_loginid);
SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT
notifid FROM notifications_seen WHERE notif_loginid=2);
Description: Reported by Duncan Hill: 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 How to repeat: Tables (dhill_notifications_for_reznichenko.tar) were uploaded to the ftp.mysql.com:/pub/mysql/Incoming directory. SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=2); ALTER TABLE notifications_seen ADD UNIQUE unq_notifid_loginid (notifid, notif_loginid); SELECT recid, notify_title FROM notifications WHERE recid NOT IN (SELECT notifid FROM notifications_seen WHERE notif_loginid=2);