Additional strategies

So far, this chapter has covered designing a database schema for efficiency and how to write efficient SQL queries. There are several additional strategies we can employ, including fine-tuning, monitoring, and maintenance. Each of these strategies is explored in this section and uses the same book inventory and ordering example from the previous section.

Fine-tuning

We can fine-tune our database server’s configuration parameters to ensure that our queries make efficient use of resources. This fine-tuning can be categorized as follows:

• Database server parameters
• Memory allocation: It is important to ensure we allocate sufficient memory for buffering and caching. For example, we can adjust the innodb_buffer_pool_size parameter in MySQL with the SET shared_buffers = '3GB'; SQL statement.
• Connection pooling: As detailed in Chapter 10, Connection Pooling, we can pool our database connections to reduce overhead and improve overall...