You could also consier moving to a document oriented database like couchdb.
I am assuming you want to show total impressions per listing of a client. Instead of calculating the total impression on http request, you could run shell scripts to periodically calculate total impressions per listing using php cli. This would not overload web server and php cli would be more robust and can run infinitely. Or try vertical scaling, get a bigger machine or use sharding to divide work among various machine etc. Another option to divide your tables alphabaetically like A to E in one table, F to h in another. this would keep the total rows low. You should also never have to calculate total impressions to a listing on runtime. I would say mysql isnt really made to collect impression data, too many inserts and beyond 10 million you are in trouble. Hope this helps, Adi www.appliedeye.com On Thu, Sep 17, 2009 at 10:52 AM, Neven MacEwan <[email protected]> wrote: > > 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] -~----------~----~----~----~------~----~------~--~---
