...

View Full Version : Simple update strangely slow



bauhsoj
09-21-2006, 08:05 PM
The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records.

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.

guelphdad
09-21-2006, 08:11 PM
how many columns are indexed on the table?

bauhsoj
09-22-2006, 03:04 PM
About 20 columns. The table holds a very large amount of information.

guelphdad
09-22-2006, 06:43 PM
then that is why your update would be slow. indexes speed up selects and slow down updates and inserts.

bauhsoj
09-22-2006, 07:47 PM
So I have to choose between having slow updates or slow selects?? :confused:

Is there no way to optimize both?

NancyJ
09-22-2006, 08:16 PM
You should consider whether you REALLY need all those 20 indexes - go through your queries and find out which indexes are actually being used.

Fumigator
09-22-2006, 10:54 PM
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.

bauhsoj
09-22-2006, 11:36 PM
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.

bauhsoj
09-22-2006, 11:44 PM
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.

NancyJ
09-23-2006, 08:31 AM
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.

SQL_F1
09-28-2006, 09:44 PM
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

bauhsoj
10-04-2006, 04:22 PM
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.

bauhsoj
10-04-2006, 04:23 PM
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.

GJay
10-04-2006, 07:52 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum