jaymc Posted December 11, 2007 Share Posted December 11, 2007 I have been looking at my website for queries that produce the same data for everyone and rearly change Or, data that can be displayed as static for 5 minutes, for example, members online can be a snapshot of who has been online since 5 minutes ago With this, I am creating html files with the output, and including it where the sql would have run on my website I then have a cronjob execute a master script which executed the sql and re populates the cache This is working really well, however, I have wondered about this I have a page that is the most accessed page on my website It queries a table with 4.5 million rows in it. The table contains a username and a staff name Each page is specific to the username, and will pull out the staff names for each username. So, its not really static However, the table rearly has new data added to it (every hour), so its nearly always going to pull out the same data# I am not to sure about using my html cache as that would mean having a folder called 'staffcache' with a txt file for each user, which would be well over 100,000 I dont really like the idea of having 100,000+ cache txt files in a folder for each user. It kind of takes away the whole normalisation and management mysql offers What is the best way around this. Should I just rely on MYSQL query cache. I know flat file cache includes would be a lot better, and would like to try my best to avoid connecting to the database to pul out data that is almost always going to return the same result Any ideas? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 11, 2007 Share Posted December 11, 2007 Look into memcached. Quote Link to comment Share on other sites More sharing options...
jaymc Posted December 11, 2007 Author Share Posted December 11, 2007 Great! Looks amazing! Quote Link to comment Share on other sites More sharing options...
jaymc Posted December 11, 2007 Author Share Posted December 11, 2007 Its true that when ever a table is updated/new row written the entire cache relating to that table is deleted? On my biggest table which has 4.5 million rows updates are done by the second, yet I can run a WHERE query in mili seconds. I assumed cache was the reason for this, but surely not if there practically is no cache? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 11, 2007 Share Posted December 11, 2007 Its true that when ever a table is updated/new row written the entire cache relating to that table is deleted? On my biggest table which has 4.5 million rows updates are done by the second, yet I can run a WHERE query in mili seconds. I assumed cache was the reason for this, but surely not if there practically is no cache? Yes, that's true... but the query speed is probably due to the indexes. Quote Link to comment Share on other sites More sharing options...
fou2enve Posted December 11, 2007 Share Posted December 11, 2007 you can also use ADODB to help with caching and it makes it easier to port the db if you ever need to. Quote Link to comment Share on other sites More sharing options...
jaymc Posted December 13, 2007 Author Share Posted December 13, 2007 Ah ok, so mysql cache is basically useless in most heavy scale situations I have looked into mem cache and it looks really good Is this the best then? Should I bother looking about or is this known to be the best from a bunch of collected benchmarks Also, any one experiences any drawbacks with using it? Quote Link to comment Share on other sites More sharing options...
fenway Posted December 13, 2007 Share Posted December 13, 2007 There was a recent post on planet mysql about memcached integration with mysql.... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.