How long is this gonna take

Backslider · #1 (**permalink**) 06-12-11, 02:05

I have a two column table with a composite unique key. At some point another dev added another index to one of the columns, which of course is a waste of space.

In phpmyadmin I clicked to delete the extraneous index, but without first giving due thought to what I was doing.

The problem is that the table has over 38 million rows of data.....

How long this gonna take to complete?... already been a couple of hours

mike_bike_kite · #2 (**permalink**) 06-12-11, 05:42

Seeing as there are only 2 fields perhaps you could detail what they are and what the indexes were and how the data is normally accessed. Then we can say whether the indexes are useful or not.

healdem · #3 (**permalink**) 06-12-11, 07:09

bit late for that, having already chopped one index

my guess would be that the PHP script or SQL has timed out and the exection has halted.

Backslider · #4 (**permalink**) 06-12-11, 11:35

The index was extraneous. Only the composite unique key was required.

Dropping the index took several hours due to the size of the table, however it saved over 700mb of space.

Always leary of phpmyadmin as it can be unpredicatable..... but this time it did what it should

mike_bike_kite · #5 (**permalink**) 06-12-11, 12:24

Quote:

Originally Posted by Backslider

The index was extraneous.

So you said in your first post but I was simply trying to see whether you were correct in that assumption or whether other parts of the system are now likely to run slower.

Backslider · #6 (**permalink**) 06-12-11, 12:50

The table is used for access control with userID plus a dataID for the data being accessed, so no, there is only a need for the composite key. Some may argue that dataID key is required for DELETE, but its not.

The disk space saving is significant.

mike_bike_kite · #7 (**permalink**) 06-12-11, 14:34

Quote:

Originally Posted by Backslider

The table is used for access control with userID plus a dataID for the data being accessed, so no, there is only a need for the composite key. Some may argue that dataID key is required for DELETE, but its not.

Would anyone want to access the data using the dataID without a userID? say to find out which users have a given dataID. Do you feel the existing composit key would cover this?

Backslider · #8 (**permalink**) 06-12-11, 15:02

No, it is never accessed in such a way, nor will ever be, but yes the existing key would suffice, because even though it is composite, one part can be used:

Code:

SELECT userID
FROM `access_master`
WHERE dataID = 62
LIMIT 0 , 30

// Showing rows 0 - 29 (2,917 total, Query took 0.0141 sec)

Clearly no table scan there

The concern, and reason for delete, was the amount of disk space required for the extraneous key. While watching my browser tick over waiting for that to complete, I became concerned since I am not familiar with how phpmyadmin goes about the task, plus knowing that it can fail with as simple a task as producing a dump from a large table. Thankfully it did not fail on this and corrupt the table.

mike_bike_kite · #9 (**permalink**) 06-12-11, 15:13

Oh well - if there's nothing to fix I'll leave it then.

Internet Services

Web Development

Digital Marketing

Consumer Tech