Heya Greg, Find a slow running query, then manually execute it with the EXPLAIN syntax. This will show how MySQL constructs the query, what indexes it does or doesn’t use, and if it uses a full table scan etc.
Info on slow logging is here: http://dev.mysql.com/doc/refman/5.4/en/slow-query-log.html The explain process is here: http://dev.mysql.com/doc/refman/5.4/en/using-explain.html If the EXPLAIN summary seems to be ok (ie: using the correct indexes, no full table scans, or adverse joins) you may need to look at changing your index type - or look at your disk I/O (partitioning may be needed?) ~ C -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of gregor brabyn Sent: Thursday, 17 September 2009 3:40 p.m. To: [email protected] Subject: [phpug] [OT] Large Database Table Problems 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] -~----------~----~----~----~------~----~------~--~---
