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]
-~----------~----~----~----~------~----~------~--~---
