Duncan Hill <[EMAIL PROTECTED]> wrote: > Mysql version: 4.1.1 > Platform: Linux, pre-compiled RPMs from mysql.com > [skip] > > 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 >
We need tables to be able to reproduce this behavior. Please, upload them to ftp.mysql.com:/pub/mysql/Incoming and let me know file names. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
