GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

"UPDATE x SET y = y + 1" taking very long?

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

  • "UPDATE x SET y = y + 1" taking very long?

    Hi,
    we have performance issue that I do not understand. For some reason query

    UPDATE query_cache SET hit = hit + 1 WHERE match_id = 1

    takes very long (could be seconds) on even modest size database, and creating index for match_id does not seem to matter match. Shouldn't that be much faster? Or is there more efficient way to do "hit = hit + 1" ?

    Here is the database structure and other info:

    Table comments: InnoDB free: 124928 kB

    Field Type Null Default
    query_id bigint(20) No
    firstword_id bigint(20) No
    query varchar(255) No
    time int(11) No 0
    numres int(11) No 0
    hit int(11) No 0
    match_id bigint(50) No 0
    query_review tinyint(4) No 0
    scheduled int(11) No 0
    timestamp timestamp Yes CURRENT_TIMESTAMP



    Indexes:
    Keyname Type Cardinality Field
    PRIMARY PRIMARY 48004 query_id
    firstword_id INDEX 48004 firstword_id
    query
    hit
    query_review
    query INDEX 48004 query



    Space usage:
    Type Usage
    Data 5,648 KB
    Index 10,304 KB
    Total 15,952 KB

    Row Statistics:
    Statements Value
    Format Compact
    Rows 45,716
    Row length ø 126
    Row size ø 357 Bytes
    Next Autoindex 59,451
    Creation Feb 16, 2007 at 10:08 AM

  • #2
    Quote:

    Shouldn't that be much faster?

    It should. I guess there's something very wrong with your setup.
    Could you show explain for corresponding select? Also, what's cardinality of match_id index?

    Comment

    Working...
    X