Designing a well-structured, normalized database schema is just half of the optimization puzzle (albeit an important half). The other half is building and fine-tuning the server that will house your database. Think about the four main components of a server: CPU, memory, hard drive, and operating system. Each of these need to be up to speed or no amount of design or programming will make your database faster!
If you put the proper pieces together at the system level, you'll have taken several steps toward overall server optimization. Using the benchmark() FunctionYou can perform a quick test of your server speed using the benchmark() MySQL function to see how long it takes to process a given expression. You can make the expression something simple, such as 10+10, or something more extravagant, such as extracting pieces of dates. No matter the result of the expression, the result of benchmark() will always be 0. The purpose of benchmark() is not to retrieve the result of an expression, but to see how long it takes to repeat the expression for a specific number of times. For example, the following command executes the expression 10+10 one million times: mysql> select benchmark(1000000,10+10); +--------------------------+ | benchmark(1000000,10+10) | +--------------------------+ | 0 | +--------------------------+ 1 row in set (0.08 sec) This command executes the date extraction expression, also one million times: mysql> select benchmark(1000000, extract(year from now())); +----------------------------------------------+ | benchmark(1000000, extract(year from now())) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (0.12 sec) The important number is the time in seconds, which is the elapsed time for the execution of the function; the first test took 0.08 seconds and the second took 0.12 seconds. You might want to run the same uses of benchmark() multiple times during different parts of day (when your server is under different loads) to get a better idea of how your server is performing. |