Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Simple update strangely slow

    The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records.
    Code:
    UPDATE items SET item_views = item_views + 1 WHERE id = 5897;
    Is there any way I could speed this up? Some setting I could change to make MySQL faster for this?

    The field "id" is the primary table key.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    how many columns are indexed on the table?

  • #3
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    About 20 columns. The table holds a very large amount of information.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    then that is why your update would be slow. indexes speed up selects and slow down updates and inserts.

  • #5
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    So I have to choose between having slow updates or slow selects??

    Is there no way to optimize both?

  • #6
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    You should consider whether you REALLY need all those 20 indexes - go through your queries and find out which indexes are actually being used.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You also might want to look at your table layout and see if you can improve upon it-- 20 fields for one table is pushing it imo.

  • #8
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    You also might want to look at your table layout and see if you can improve upon it-- 20 fields for one table is pushing it imo.
    I have been looking at that for a while. Problem is, changing that tables structure would require a massive change in the way the corresponding PHP code works with it. Not that we aren't working on that....it is just a long term project and I am looking for an intermediate solution in the meantime.

  • #9
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NancyJ View Post
    You should consider whether you REALLY need all those 20 indexes - go through your queries and find out which indexes are actually being used.
    Would indexes with a very low cardinality really benefit from having it's own index? For instance, there are a few indexes on this table with a cardinality of 1, 2, and 7. These aren't fulltext or composite indexes.

  • #10
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    with a cardinality of less than apprx 30% then there is little benefit - if you run all your queries with 'EXPLAIN' before them - the output will show you the possible keys for a query and which keys are being used.

    Without seeing your table structure and your queries its hard to advise.
    But you really need to find out which indexes are being used - first,and secondly the effectiveness of those indexes.

  • #11
    New Coder
    Join Date
    Sep 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The choice you speak of is the difference between OLTP (on-line transaction processing) databases and OLAP (on-line ANALYTICAL processing).
    OLTP = lots of updates and inserts, few(er) selects.
    OLAP = few updates and inserts (usually in batches at night), many selects.

    The structures are different to optimize for their respective purposes.

    The classic way of dealing with table changes while code continues to run is to use views that correspond to the existing table structures while the underlying tables are changed.

    You appear to be in the classic corner with the choice being to get comfortable there and hope things don't get worse as time goes by, or create an optimisation plan, particularly if this is a mission critical database for your company

    Good Luck
    Brian

  • #12
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Would removing such indexes with a low cardinality boost speed any? Those are used for sorting sometimes but they don't have many possible values.

  • #13
    Regular Coder
    Join Date
    Jan 2005
    Posts
    470
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Would moving the fields that are updated frequently to their own table boost speed significantly? There are three fields that are regularly updated in that table.

  • #14
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    the best thing is to try it and see.

    setup some code that will run a fairly large number of queries that are representative of your real application, and time how long they take with different index combinations.


  •  

    Posting Permissions

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