0

i have a table which contains two columns: dte and id. together, these two identify a unique record. is there a difference in access time between setting a primary key using these two columns or just creating a regular index like so:

create index idx on mytable (dte, id)
create index idx2 on mytable (id)

i am looking for an answer specifically in terms of access time for joins with other tables that also have these two columns. in particular what seems weird is when i do explain on a join, it tells me it needs to examine 15 rows in one of the tables where this pairing is once again unique.

Alex
  • 19,533
  • 37
  • 126
  • 195

1 Answers1

0

In short, the primary key identifies a row on disk. Other indexes contain a link to the primary key (at least, in InnoDB). Indexes are supposed to live in memory and send the db engine to the disk for the lacking data. If the index contains all data required for a query (so that the engine does not need to go on disk), the index is called covering for the query.

If you are going to match your rows by both columns at once (or always output both of them), than both shown indices are going to be covering, and the speed is going to be relatively the same (depending on the selectivity).

But the index itself requires RAM. In case of InnoDB, any non-primary key contains a copy of the primary key. Thus, in your case it might be better to include the second field into the primary key.

This is all theory, in practice I have not understood your problem.

newtover
  • 31,286
  • 11
  • 84
  • 89