Primary key for a table where DATETIME col is used for almost all SELECT operations

I'm wondering what kind 开发者_JAVA技巧of PK I should be choosing for this table in MySQL. Almost all the SELECT operations will involve the DATETIME (date ranges, a specific date, etc.).

Is there a best practice for this?

I wouldn't recommend that the DATETIME be your PK, but you should certainly create an index on that column.

It's perfectly acceptable to use dates to form part of a composite primary key especially if you're using innodb and want to take advantage of clustered primary key indexes to gain maximum read performance.

have a look at the following:

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

MySQL script

Things to note:

• innodb doesnt support auto_increment composite primary keys hence the use of the sequence table.

• the auto_increment portion of the primary key just helps guarantee uniqueness - the outer part of the key is the important part i.e the date.

full script here: http://pastie.org/1475625 or continue reading...

drop table if exists foo_seq;
create table foo_seq
(
next_val int unsigned not null default 0
)
engine = innodb;

insert into foo_seq values (0);

drop table if exists foo;
create table foo
(
foo_date datetime not null,
foo_id int unsigned not null, -- auto inc field which just guarantees uniqueness
primary key (foo_date, foo_id) -- clustered composite PK (innodb only)
)
engine=innodb;

delimiter #
create trigger foo_before_ins_trig before insert on foo
for each row
begin
declare v_id int unsigned default 0;
    select next_val+1 into v_id from foo_seq;
    set new.foo_id = v_id;
    update foo_seq set next_val = v_id;
end#

delimiter ;

Stats:

select count(*) as counter from foo; -- count(*) under innodb always slow
+---------+
| counter |
+---------+
| 2000000 |
+---------+

select min(foo_date) as min_foo_date from foo;
+---------------------+
| min_foo_date        |
+---------------------+
| 1782-11-21 16:32:00 |
+---------------------+
1 row in set (0.00 sec)

select max(foo_date) as max_foo_date from foo;
+---------------------+
| max_foo_date        |
+---------------------+
| 2011-01-18 23:06:04 |
+---------------------+
1 row in set (0.00 sec)


select count(*) as counter from foo where foo_date between 
    '2009-01-01 00:00:00' and '2011-01-01 00:00:00';

+---------+
| counter |
+---------+
|   17520 |
+---------+
1 row in set (0.01 sec)

select * from foo where foo_date between 
  '2009-01-01 00:00:00' and '2011-01-01 00:00:00' order by 1 desc limit 10;

+---------------------+--------+
| foo_date            | foo_id |
+---------------------+--------+
| 2010-12-31 23:06:04 |    433 |
| 2010-12-31 22:06:04 |    434 |
| 2010-12-31 21:06:04 |    435 |
| 2010-12-31 20:06:04 |    436 |
| 2010-12-31 19:06:04 |    437 |
| 2010-12-31 18:06:04 |    438 |
| 2010-12-31 17:06:04 |    439 |
| 2010-12-31 16:06:04 |    440 |
| 2010-12-31 15:06:04 |    441 |
| 2010-12-31 14:06:04 |    442 |
+---------------------+--------+
10 rows in set (0.00 sec)

explain
select * from foo where foo_date between 
  '2009-01-01 00:00:00' and '2011-01-01 00:00:00' order by 1 desc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  |rows  | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | foo   | range | PRIMARY       | PRIMARY | 8       | NULL |35308 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

Pretty performant considering there are 2 million rows...

Hope this helps :)

Choose an autonumber in order to be on the safe side. You could have two or more rows with the same datetime.