primary key based on 2 columns - error?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I have the following table Im using as a 'history' of usage
for users:

create table history
    userid varchar(20) not null,
    dt datetime not null,
    primary key(userid, dt)

(other members in table not shown)

I want to be able to use 2 columns to speed up the
search over a hundred thousand or more 'userid's based
on the times they accessed the site.

When I insert records into the table, I get back this

Duplicate entry 'myuserid' for key 'PRIMARY'

This is my insert: (other members also not shown)

INSERT INTO history (userid,  dt ) VALUES ( '$userid',  now() )";

Im using mysql 5.1.48

Is this a real error or a bug?  Or did I do something wrong?
Thanks in advance.

Re: primary key based on 2 columns - error?

responding to wrote:
Bible Trivia wrote:

Quoted text here. Click to load it

2-column indexes are only helping where you're selecting using both
columns in the WHERE clause as in

SELECT * FROM `history` WHERE `userid`='x' AND `dt` BETWEEN `123454677'
AND '12345689'; , that sort of a deal. Also, they are only helping in
cases where you have significantly more selects than inserts because
you're going to loose any benefit of faster select by having to wait until
a longer (maybe twice longer) index update is going on the table after
EACH insert. Well, unless you can combine your multiple INSERTS into one
multi-row INSERT ... but, in any case, it's worth a test before
implementing it in hopes of getting some optimization benefit.

Quoted text here. Click to load it

PRIMARY key wants to be unique, that's why it won't insert a duplicate
value. You will have to re-create that index as just an INDEX, not
PRIMARY, if duplicate entries are possible.


Re: primary key based on 2 columns - error?

Quoted text here. Click to load it

Whether your approach will succeed in speeding anything up depends
on the query used to access the data.  For example, if you are
trying to search WHERE dt BETWEEN xxxxxx AND yyyyyy, having the
two-part index won't help at all.  If you are trying one user at a
time (WHERE userid = '$userid' AND dt BETWEEN xxxxx AND yyyyy ),
it will.

Quoted text here. Click to load it

I don't believe that this error message came from attempting to
insert data into the table above.  The error message should have
looked something like:

Duplicate entry 'myuserid-2010-12-27 01:02:03' for key 'PRIMARY'

if you actually had a multi-part key.  Please spell the error
message exactly as it appeared,

Quoted text here. Click to load it

I think you did something you're not telling us.  The error message
doesn't match the attempt.

Because you have a two-part primary key, MySQL will not permit you
to insert two records having both the same userid and the same time.
Is it possible to have one user generate two records in the same
second?  If so, you're going to get errors (and lose records) in
that situation.  If it is legitimate for one user to generate two
records in the same second, you don't have a good primary key (or
at least not one involving fields you've show us), and you should
use INDEX(userid, dt) which does not require uniqueness to avoid
dropping records.

Site Timeline