Mysql – unable to add index to column, duplicate entry

MySQL

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.

Best Answer

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

Related Solutions

MySQL Index Issue – Select Distinct Order By Limit Query Stops Using Index

I think I've found a workaround, though I'm going to file a bug report unless someone can explain why this works.

The problem appears to originate with the id portion of the order by clause. When using either

order by name asc,
         name asc,
         id asc

order by name asc,
         id asc,
         id asc

then the query uses the existing indices on related_id and the results come back instantly. I had another similar query, and it was solved by changing this

order by id asc

to this

order by id asc,
         id asc

This makes absolutely no sense to me, but it works, and it all seems to be focused on the presence of order by id.

Mysql – Why isn’t this index helping the InnoDB MySQL query

This is just a guess, as I do not have all info, but you probably would be better by doing:

EXPLAIN SELECT STRAIGHT_JOIN
    *
FROM
    tusers PARTITION (p362) tu
    JOIN users PARTITION (p362) u
      ON u.group_id=tu.group_id 
      AND tu.email_address=u.email
      AND tu.group_id = 362 
WHERE
    tu.application_id=253555;

Note the STRAIGHT_JOIN, that may not be needed -if it is needed, then I may have assumed wrongly- and the tu.group_id comparison (that, again, shouldn't be needed).

Then using the following keys:

(tu.application_id, tu.group_id, tu.email_address)
(u.group_id, u.email)

However, if the number of records to be returned is 2.5M, as your cardinality suggests, then do not expect this to be fast... this is a pure IO math.

There are many other things that clicks me as problems, but I cannot say for sure without access.

Those could be even more effective if you didn't do a SELECT *.

Another thing is that varchar(255) is usually a bad idea.

Best Answer

Related Solutions

MySQL Index Issue – Select Distinct Order By Limit Query Stops Using Index

Mysql – Why isn’t this index helping the InnoDB MySQL query

Related Question