Hi, I wonder if any one can help me with a performance issue. I've tried a number of different solutions to this problem, but haven't found something that is really good enough yet. The current set up is this. I have two InnoDB tables (master/slave) and a cron job script which runs daily at midnight. The cron script will process feeds and insert/update the master table and is currently processing about 140,000 records, once complete the slave table is updated. It inserts any missing records and ONLY updates the records that have been altered by comparing the date modified dates. The date modified is only updated in the master table if a change is actually made. This seems the best approach to this regardless of my actual problem. The problem I'm having however is that the cache queries on my site are now flushed which is pushing up the page load times to about 10-12 seconds, even with an index on the table as it would seem that this is also flushed. Does any one know any way to increase the performance of this? Ideally I don't want to have to rely on the cache and the queries should be executed quickly from the start. I'm open to any suggestions. For an exmaple of this, you can see it here: http://beta.instarentals.com/holiday-rentals/ It's the rental browser on the left and the two queries in the footer that are causing the issues. I have cleared the cache and put SQL_NO_CACHE into the statements so you can see the load times. Thanks in advance.
Sorry, managed to work something out. I made the browser on the left use ajax and cached the queries. Read a number of performance and optimisation blogs and managed to tweak certain tables and queries to allow pretty much every single page be no more than 0.2 seconds.
You can set the query cache limits in the my.cnf and that will improve the caching efficiency of MySql