Announcement Module
No announcement yet.

performance tuning on simple update query

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • performance tuning on simple update query


    here is the simple update query which takes more than 20sec :roll:

    UPDATE audience_member_reg SET completed_page_no = 2, reg_datetime = '2009-03-28 07:13:45' WHERE audience_member_reg_id = 6640737;

    explain statements are,

    mysql> explain select completed_page_no,reg_datetime from audience_member_reg where audience_member_reg_id = 6640737\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: NULL
    type: NULL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: NULL
    Extra: Impossible WHERE noticed after reading const tables
    1 row in set (0.00 sec)

    is there anyway this query can be improved upon? could anyone please help me out. thanks for your response.

  • #2
    Use explain with valid audience_member_reg_id.

    What is the type of the table: myisam or innodb ?
    How many rows are there ?


    • #3
      Thanks for you reply.

      this is Innodb engine. this table has nearly 5 millon of records.

      and audience_member_reg_id is the primary key.


      • #4
        What are your InnoDB settings?

        If you have nearly 5 million records, you innodb_buffer_pool_size should be set to at least 128MB. If you're only running MySQL/InnoDB on the machine, you can safely set it to 75% of your RAM (though there's little point setting it bigger than the size of your database).