Have Geek Brain

Is it useful to use temporary tables?

We have a mySQL database table for products

. We use the cache tier to reduce the load on the database, but we believe it is a good idea to minimize the actual data that needs to be stored in the cache tier to speed up the application.

All products in the database that are visible to visitors have a price to them:

Prices are stored in another table called prices

. There are several price categories, depending on what level of discounts apply to each visitor (client). From time to time, there are campaigns that mean there is a special price available for each product. Special prices are stored in a table called specials

.

  • Is it bad to make a temp table that links tables together?

It will only have the information it needs and therefore will be cached.

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

Thus, it would be very easy to know if a particular product actually has a price, without having to iterate over the entire table prices

or specials

every time a product has to be listed or presented.

  • Are there temporary tables for web applications, or is this just bad design?
+2


a source to share


2 answers


You should approach it like you would any other performance problem. Decide what performance you need, then try testing it again on production-grade hardware in your lab. Don't make unnecessary optimizations.

You review your application and find out if it is making too many requests or the requests themselves; Most of the time, the slowness of web applications is caused by too many requests (in my experience), although the requests are very simple.



Generally, the best engineering solution is to restructure the database, in some cases denormalization, so that normal read applications require fewer queries. Caching can be useful as well, but refactoring often requires better query counts.

As such, you can increase the amount of work in the write path to reduce the amount in the read path if you plan on doing much more reading than writing.

+1


a source


If you are going to cache this data anyway, should it really be in the temp table? You would only incur the query overhead when you needed to rebuild the cache, so the temporary table might not even be needed.



+2


a source






More articles:


All Articles