Optimizing at the Hardware Level Optimizing at the Hardware Level Balancing Portability and Performance 8.2 Optimizing SQL Statements 8.2.1 Optimizing SELECT Statements 1640 The main considerations for optimizing queries are: NDB Cluster supports a join pushdown optimization whereby a qualifying join is sent in its entirety to NDB Cluster data nodes, where it can be distributed among them and executed in parallel. For more information about this optimization, see Conditions for NDB pushdown joins. Besides  SELECT  statements, the tuning techniques for queries also apply to constructs such as  CREATE TABLE...AS SELECT ,  INSERT INTO...SELECT , and  WHERE  clauses in  DELETE  statements. Those statements have additional performance considerations because they combine write operations with the read-oriented query operations. Queries, in the form of  SELECT  statements, perform all the lookup operations in the database. Tuning these statements is a top priority, whether to achieve sub-second response times for dynamic web pages, or to chop hours off the time to generate huge overnight reports. The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines in this section help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and write data, the behind-the-scenes overhead for SQL operations in general, and operations used in specific scenarios such as database monitoring. To use performance-oriented SQL extensions in a portable MySQL program, you can wrap MySQL- specific keywords in a statement within  /*! */  comment delimiters. Other SQL servers ignore the commented keywords. For information about writing comments, see Section 9.7, “Comments” . Any database application eventually hits hardware limits as the database becomes more and more busy. A DBA must evaluate whether it is possible to tune the application or reconfigure the server to avoid these bottlenecks, or whether more hardware resources are required. System bottlenecks typically arise from these sources: •Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk. •Disk reading and writing. When the disk is at the correct position, we need to read or write the data. With modern disks, one disk delivers at least 10–20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks. •CPU cycles. When the data is in main memory, we must process it to get our result. Having large tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem. •Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.