CODEWITHSUNDEEP
mysqlperformancedatabase-designscalabilitytemp-tables
Industrial
Industrial

Reputation: 42798

Is it wise to use temporary tables?

We have a mySQL database table for products. We are utilizing a cache layer to reduce database load, but we think that it's a good idea to minimize the actual data needed to be stored in the cache layer to speed up the application further.

All the products in the database, that is visible to visitors have a price attached to them:

The prices are stored in a different table, called prices . There are multiple price categories depending on which discount level each visitor (customer) applies to. From time to time, there are campaigns which means that a special price for each product is available. The special prices are stored in a table called specials.

It would only have the neccessary information and would ofcourse be cached.

-------------|-------------|------------ 
| productId  |  hasPrice   | hasSpecial
-------------|-------------|------------ 
  1          |  1          | 0
  2          |  1          | 1

By doing such, it would be super easy to know if the specific product really has a price, without having to iterate through the complete prices or specials table each time a product should be listed or presented.

Upvotes: 4

Views: 3908

Answers (2)

MarkR
MarkR

Reputation: 63616

You should approach it like any other performance problem: Decide how much performance is necessary, then iterate doing testing on production-grade hardware in your lab. Do not do needless optimisations.

You shoud profile your app and discover if it's doing too many queries or the queries themselves are slow; most cases of web-app slowness are caused by doing too many queries (in my experience) even though the queries are very easy.

Normally the best engineering solution is to restructure the database, in some cases denormalising, to make the common read use-cases require fewer queries. Caching may be helpful as well, but refactoring so you need fewer queries is often the best.

Essentially you can increase the amount of work on the write-path to reduce the amount on the read-path, if you are planning to do a lot more reading than writing.

Upvotes: 2

Eric Petroelje
Eric Petroelje

Reputation: 60549

If you're going to cache this data anyways, does it really need to be in a temp table? You would only incur the overhead of the query when you needed to rebuild the cache, so the temp table might not even be necessary.

Upvotes: 3

Related Questions