Hi Gregor, index behaviour changes sometimes over time, because the relative size in regards to other tables may change.
To resolve such problems, you can always (and you probably have in the past) - find our which query takes long. Do this as a sum of time each query uses (you may run the same query 50000 times) - run EXPLAIN your query - post back the results here Kind Regards, Jochen Daum Chief Automation Officer Automatem Ltd Phone: 09 630 3425 Mobile: 021 567 853 Email: [email protected] Skype: jochendaum Website: www.automatem.co.nz http://twitter.com/automatem http://www.xing.com/go/invite/3425509.181107 http://php.meetup.com/10 On Thu, Sep 17, 2009 at 3:40 PM, gregor brabyn <[email protected]> wrote: > > 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] -~----------~----~----~----~------~----~------~--~---
