1

I have a slightly complex SELECT query which uses a number of JOINs and variables inside it and the query is run frequently throughout the application.

The result varies when one column is updated. Eg: When the product quantity is updated only, the result set changes.

So my question is, is it a good idea to run a trigger and update a temporary table in the database, with my SELECT queries results, whenever the product quantity is updated? Or to run the complex SELECT query every time?

Right now I tested with ~10 rows of data and the query execution time is 0.001 seconds. I want to use the best possible method which won't hinder the performance as the DB grows.

Thank you!

2

In general, a question about optimization should always be proceeded by an example of why you need it. If you can query a database and get the result you need back in 0.001 seconds, then you have nothing to worry about--a database will happily return all the information you need.

Remember, just because you think the query is complex doesn't mean the database engine thinks so. A good database engine is built to query large amounts of data quickly. It will find a way to make your query run as efficiently as it can.

Now, if you come back and say that you can't generate the information you need in less than 30 seconds or that the number of transactions overwhelms your server, you might have a case to dump the data into a temporary table. However, you need to be careful about that--it's not as easy as pulling the data out of a table, you need to make sure that users only get one result, rather than parts of two as you re-generate the table. But my advice stands--you ain't gonna need it until you need it.

2
  • Great. What I was worried about was that after the DB grows, whether it would be a good solution to keep a temp table. Thanks for clearing out my doubt for me. – blackpla9ue Feb 19 '14 at 8:57
  • 2
    @blackpla9ue: just insert as much fake data into the database as you think it will grow to, times three, and see how it behaves. Performance work must always be based on measurements in order to be effective. – Joeri Sebrechts Feb 19 '14 at 9:36
0

Since this is tagged MySQL... MySQL already has built in support for query caching. So there shouldn't be a need for you to roll your own unless you had very specific, unique challenges to get around. You just want to make sure query caching is enabled in your MySQL installation. It would not surprise me if most other DBMS's had similar functionality.

So, generally speaking, no, there shouldn't be a need for this for queries themselves.

If you are concerned about query performance, learn how to use EXPLAIN, and interpret the results to help you build indexes. That is where you'll get the best 'bang for the buck' in terms of performance tuning.

2
  • But query caching doesn't work where ever variables are involved, right? – blackpla9ue Feb 19 '14 at 8:54
  • I believe it can cache queries that use bound parameters. But you'll have to read the docs for specifics. – GrandmasterB Feb 19 '14 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.