Professional Web Applications Themes

slow query on big table - MySQL

I have a table with more than 3,000,000 records. Frequently, I have to do an update on this table that is taking a long time and I'd like some advice as to what I can do to fix the problem. Let's call the table students. The primary key is an auto-indent ID which is unique for each student. However, the table itself basically works to connect students with groups and campaigns they are associated with. Because of this a student might exist in the table more than once. When I do this update I want it to change information in ...

  1. #1

    Default slow query on big table

    I have a table with more than 3,000,000 records. Frequently, I have to
    do an update on this table that is taking a long time and I'd like some
    advice as to what I can do to fix the problem.

    Let's call the table students. The primary key is an auto-indent ID
    which is unique for each student. However, the table itself basically
    works to connect students with groups and campaigns they are associated
    with. Because of this a student might exist in the table more than
    once. When I do this update I want it to change information in each of
    the instances of that student, so instead of updating based on the
    unique id in Students I have to use an identifier that is common to the
    student regardless of which campaign or group they belong to. Let's say
    this is their phone number, which is an integer for the purposes of
    this question. I realize this is a poor example, but for the purposes
    of the question it'll work.

    So, the table is indexed not only on the unique identifier, but on the
    phone number as well. When I do a SHOW INDEX on this table it shows me
    both of these. However, when I do an explain on both, the unique index
    shows me that it will have to inspect 1 row while the phone number
    shows me that it will have to inspect all of the rows in the table.
    Because this table is big, this takes a long time.

    So, I guess my question is, why isn't the phone number as efficient as
    the unique indentifier? Is it because it could exist multiple times in
    the database? Is there any way to make it more efficient? is the only
    solution to this problem to make the phone number a unique key for this
    table and having associations between groups and campaigns done
    elsewhere?

    Thanks for your help. I've been researching this for a while, and any
    assistance would be greatly appreciated.

    lambelly@gmail.com Guest

  2. #2

    Default Re: slow query on big table

    com wrote: 
    updates and insert speeds are affected by the number of indexes you have,

    is there any reason you can't just use the autoincrement as the primary
    key index, and drop the other indexes (if you do you will see a lot
    faster update).
    Kim Guest

  3. #3

    Default Re: slow query on big table

    Kim Hunter wrote: 

    I could, but I'd have to redesign the table structure. As it is, a
    student might be in the table more than once and I need to update this
    information on every instance of this student in the student table. So,
    the student might be in there maybe two or three times.

    If I were to use the autoincrement I'd have to move the associations
    with groups and campaigns into a different table. Which might not be a
    bad idea.

    lambelly@gmail.com Guest

  4. #4

    Default Re: slow query on big table

    com wrote: 
    >
    >
    > I could, but I'd have to redesign the table structure. As it is, a
    > student might be in the table more than once and I need to update this
    > information on every instance of this student in the student table. So,
    > the student might be in there maybe two or three times.
    >
    > If I were to use the autoincrement I'd have to move the associations
    > with groups and campaigns into a different table. Which might not be a
    > bad idea.
    >[/ref]

    First of all, a phone number will be a char field, not an int. And
    chars fields always take longer to compare than ints.

    However, you have a bigger problem. You shouldn't have anyone in there
    more than once. (And what happens if you get two students sharing an
    apartment - and therefore the same phone number?)

    You need to normalize your database. Have one entry for a student in
    this table, and use another table with two columns - a student id and an
    organization id.

    Yes, you'll have to do a little work to do it this way, but it will cut
    down your table size, allow you to search in the (much quicker) auto
    increment field and make everything a lot faster.

    And google for tutorials on "database normalization". You'll probably
    find other places you can make this more efficient.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: slow query on big table

    Jerry Stuckle wrote:
     

    Yeah. I didn't design the database and kind of adopted it when I got
    this job. When I talk about normalizing it my boss doesn't really want
    to do that because of the changes we'd have to make to the software,
    which would be complicated. So, I'm hoping that there are solutions
    which will make both of us happy because right now we're hampered with
    large problems. However, it looks like all we can do is fix the
    database properly.

    lambelly@gmail.com Guest

  6. #6

    Default Re: slow query on big table

    com wrote: 
    >
    >
    > Yeah. I didn't design the database and kind of adopted it when I got
    > this job. When I talk about normalizing it my boss doesn't really want
    > to do that because of the changes we'd have to make to the software,
    > which would be complicated. So, I'm hoping that there are solutions
    > which will make both of us happy because right now we're hampered with
    > large problems. However, it looks like all we can do is fix the
    > database properly.
    >[/ref]

    With the current design, your options are going to be very limited.
    There probably isn't going to be a lot you can do to speed it up
    significantly.

    You can play with the MySQL tuning parameters some, and might get some
    gains. But I suspect since you're main problem is on an UPDATE you
    won't gain a lot.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Help in optimizing a slow query...
    By DDJ in forum MySQL
    Replies: 1
    Last Post: May 13th, 06:13 PM
  2. Slow MySql query
    By jack in forum PHP Development
    Replies: 2
    Last Post: August 31st, 08:44 PM
  3. Occasional slow query
    By AK in forum IBM DB2
    Replies: 2
    Last Post: August 19th, 04:20 PM
  4. TEMP table comes into exist very slow
    By Thomas in forum Informix
    Replies: 2
    Last Post: July 16th, 02:47 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139