Hello,
SQL beginner here.
I have a query which takes around 10 seconds to run, so we have a slow down in the application.
Would it be possible in MySQL or more generally in SQL for the server to periodically (every 1 to 5 minutes) run the query and store it somewhere, so that I can query this "cache" table for easy access ?
Many thanks in advance !
Your application could have a thread that wakes up, does the query, saves the results into a temp table, and the rest of the application would just use the temp table.
Most likely depending on what your query is, you can speed up the query by creating missing indexes on your table(s). Put EXPLAIN EXTENDED in front of your query to see which indexes it is using, and try to figure out which columns should be indexed. The columns that should be indexed are those in your where statement, as well as any fields used in joins. If you provide the query, and the table schema, we may be able to help recommend some indexes.
While there probably are quite a few ways to run a process every 5 minutes to fill a temp table (cron job, scheduled task, run a thread from your app), you would probably be better off exhausting the indexing option before taking drastic measures such as this, which may just use more resources. and may not necessarily increase the performance of your application.
There's no such scheduling provision in MySQL.
However you can always run a scheduled job (via at/scheduler on Windows, cron or whatever your scheduling system you use on Unix) to execute such a "execute and insert results into staging table" query/procedure.
Aside from your direct question, also look at optimizing the query - it's possible that judicious application of indexing and/or re-architecting the tables might speed it up if you have not done so yet (but I fully admit the some queries are optimized as best as they can be)
Another solution is streaming/chunking - have the app retrieve "next N rows" on demand/need.
Also, consider caching the query results in teh app itself instead of in DB - that way cache does not require DB access and is even faster.
Granted this solution is probably beyond your scope right now. You can use an ORM Framework to perform the caching for you. Basically what you are asking for is for a caching solution for your results. The caching solutions can vary based on the vendor and language you are using.