Gregor I'm not a MySQL expert but I'd suggest from what you have said the index has become fractured, In MS SQL you have clustered and non clustered indexes (clustered is grouped with the data) and ideally you want your clustered indexed column to be sequential (so that it can build the B tree as it goes).
Does mysql have a rebuild index function, if not drop and recreate it, if performance improves this is the prob HTH Neven > Earlier this year I emailed this group about a problem I had with a database > listing_impressions table getting too big (over 20 million rows) and this was > making parts of a customers website run way too slow. > > Impressions are constantly being inserted into this table and at the same > time our customers clients will log in and want to view how many impressions > their listing has received (resulting in select queries to the table). They > get to view total impressions ever received and impressions for each of the > last 7 days. > > We fixed this problem by making an index on 3 fields in this table > (listing_id,listing_type,timestamp). It was also advised to me that I change > the timestamp field to a date field but it did not seem necessary at the time > as the queries (& website) were running fast again. > > Problem is that just 4 or 5 months later with the database table less than > 15% bigger, queries to this table are again slowing down the website. The > database has at times been running out of database connections because of the > slowness and as a result I have (for the time being) stopped impressions > being recorded and impressions stats being displayed to stop this happening. > > Our host advises us that Yahoo! Slurp has been hitting the website a lot but > we do have in robots.txt 'Crawl-delay: 10' to slow down their rate of hitting > the website. > > Does anyone have any ideas as to why this table is slowing down the website > again? The index is still there. Ideas for solutions are welcome. > > Regards > Greg > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
