Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2011
    Posts
    40

    Unanswered: How long is this gonna take

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2011
    Posts
    40
    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

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Backslider View Post
    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.

  6. #6
    Join Date
    Jun 2011
    Posts
    40
    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.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Backslider View Post
    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?

  8. #8
    Join Date
    Jun 2011
    Posts
    40
    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.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Oh well - if there's nothing to fix I'll leave it then.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •