primary key based on 2 columns - error?

[Bible Trivia]

Hi-

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
error:

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.

[www.1-script.com]

responding to
http://www.1-script.com/forums/primary-key-based-on-2-columns-error-article7783--22.htm

Post by Bible Trivia
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.

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.

Post by Bible Trivia
When I insert records into the table, I get back this
Duplicate entry 'myuserid' for key 'PRIMARY'

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.

-------------------------------------

[Gordon Burditt]

Post by Bible Trivia
I have the following table Im using as a 'history' of usage
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.

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.

Post by Bible Trivia
When I insert records into the table, I get back this
Duplicate entry 'myuserid' for key 'PRIMARY'

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,

Post by Bible Trivia
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?

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.