i have this situation.
using latest mysql server,
after some period of inactivity my website sqls times out (only some not all)
1. when execute a simple sql like (select count(*) from products) this works fine all the time.
2. when execute below sql
SELECT [url removed, login to view] FROM products INNER JOIN catalog ON products.cid=[url removed, login to view] WHERE products.is_visible='Yes' AND ( products.inventory_control = 'No' OR [url removed, login to view] > 0 OR products.inventory_rule = 'OutOfStock' OR (products.inventory_control = 'AttrRuleInc' AND [url removed, login to view] >= 0) ) AND products.is_home='Yes' GROUP BY [url removed, login to view]
it times out ( Note it will not timeout all the time, this happens after 1 hour or 2 hour of inactivity)
1. The very first execution takes 30+ seconds and it times out and after that the above executing SQL 2 to 3 times the 4th time onwards it executes fast in 3 to 5 seconds
after 1 or 2 hour inactivity this pattern repeats.
I did not do any changes in settings.
i have 2 sites on this server.
1st server has 20,000 rows of in database ( this one works fine ) 2nd site has 150,000 rows in the databse ( this one is having this issue)
So this is something "query time out"
i do not think this is something on SQL settings, if it is then i should see this behavior on both sites
can anyone help me