0

I have a table that collects virtual machines usage metrics, I'm trying to add an index to timestamp column, to speed up queries, when selecting time, this is a sample of the table I have, it has other columns, I have only listed 3 here.

    id  timestamp            vm_name  
------  -------------------  ---------
     1  2015-05-05 18:21:50  i-2-20-VM   
     2  2015-05-05 18:21:50  i-2-21-VM   
     3  2015-05-05 18:21:50  i-2-22-VM   
     4  2015-05-05 18:21:50  i-2-23-VM   
     5  2015-05-05 18:21:50  i-2-24-VM   
     6  2015-05-05 18:21:50  i-2-25-VM   
     7  2015-05-05 18:21:50  i-2-26-VM   
     8  2015-05-05 18:21:50  i-2-27-VM   
     9  2015-05-05 18:21:50  i-2-28-VM   
    10  2015-05-05 18:21:50  i-2-29-VM   

As you can see, my application is inserting duplicate timestamp entry.

When attempting to add an index to timestamp column, I get the following error message.

Duplicate entry '2015-05-05 18:21:50' for key 'PRIMARY'

I'm not sure what do I need to change in my DB design to resolve this issue.

Your suggestions are highly appreciate it.

3
  • 2
    Can you post the create index statement?
    – zgguy
    May 5, 2015 at 19:39
  • 1
    What is the definition of the index you are trying to add? Seems a bit odd that you'd have a timestamp as a PK.
    – Philᵀᴹ
    May 5, 2015 at 19:39
  • 1
    How about simply INDEX(timestamp), not PRIMARY KEY. What is the SELECT you want to speed up??
    – Rick James
    May 6, 2015 at 4:15

4 Answers 4

1

I think you are trying to create a primary key and not a simple index. (Primary key requires all values to be unique)

To add a index in mysql follow the syntax here: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

5
  • Key and index are exactly the same in terms of mysql.
    – Kondybas
    Aug 8, 2017 at 20:00
  • 1
    @Kondybas - But key and primary key are not, correct?
    – RDFozz
    Aug 8, 2017 at 20:02
  • @RDFozz Primary keys is a subset of all keys. One and only one PK can be defined for the given table, and this and only this key is used by engine as clustered key. Speaking formal, key is the column(s) used for restrictions while index is the underlaying structure allowing to speed up and/or restrict data manipulations.
    – Kondybas
    Aug 8, 2017 at 20:11
  • 1
    @Kondybas - So, the user can build an index on that column (as they say they want to) without changing the data - they just can't build a primary key/unique index on the column. So far, this is the only answer that doesn't assume the user meant to create a primary key, when that's not what they say in the post.
    – RDFozz
    Aug 8, 2017 at 20:20
  • Your statement doesn't follows mine. Primary key has significant property of uniqueness. If one create the PK for an empty table, PK do not allow inserts/updates that violate uniqueness and such rows will be rejected. If one want to add PK on colums that already violate uniqueness, he should manually reject all the duplicates like the PK would have done.
    – Kondybas
    Aug 8, 2017 at 20:48
0

It appears you have date/time as a primary key and you have multiples of that. Two possibilities: add a primary key field that is unique. Or create a multicolumn key with date, time and id or vm_name.

-1

You have to remove all the duplicates prior to create the UNIQUE or PRIMARY (that is also UNIQUE in fact) index. In your case that can be performed via joining the table with itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z
    ON z.vm_name = w.vm_name
   AND z.timestamp = w.timestamp
   AND z.id > w.id
;

Then you have to create multicolumn primary index (vm_name, timestamp) instead of (timestamp).

-1

Try using this:

create index timestamp_index on tablename(timestamp);
1
  • 1
    You could improve your answer by explaining why this works and citing a reference. Jan 24, 2023 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.