--skip-name-resolve This option may speed things up a little bit. It causes
MySQL to use IP addresses instead of resolving the IP to a name.
-V or -version This outputs the version information of your current MySQL
installation.One way to speed up MySQL is to turn off logging, but this is not recommended. If your database were
to crash, there would be no way to get the data back. However, turning off logging does improve
performance. You just have to weigh the benefits.
Another minor performance improvement would be to use the -skip-name-resolve option. This
saves a little bit of time and should have no adverse effects on your database operation.Building a Better SQL Statement
The next place to look is at your database design and the SQL statements you are using to access your
data. A whole book could be written on the best techniques to do these things, but that is beyond the scope
of this book. This book will cover some of the basic steps you can take to help make your database faster.
The structure of your database plays a huge role in the overall performance of your database. If your
tables are not constructed properly, bad things tend to happen. The following are some general
guidelines for faster tables:
Use the smallest data type possible—The smaller the data type the less disk space
and memory it uses. The less memory, the quicker it can be retrieved and used. Keep
things small.
Force values into columns by using NOT NULL—This saves a little space and speeds
things up.
Try to avoid using variable length columns—If you have to, you have to. But if you can
get away without using variable length columns, do it. This can vastly improve
performance.
Avoid using too many indexes—Indexes speed up SELECT statements but slow down
INSERT and UPDATE statements. If you have too many indexes, it will slow down
everything. Review Day 6, "Adding Tables, Columns, and Indexes to Your Database,"
for more information on indexes.
Choose your table type(s) with an eye to performance—There are four table types in
MySQL (non-paying members only have three). They are static (default), dynamic,
heap, and compressed. The static table is created by default. It is the fastest of all
normal tables. It can only contain non-variable length columns. If it has even one
variable-length column, MySQL automatically changes it to a dynamic table. These
tables are slower—they contain more information in them (each row has to contain
information stating how big they are). The heap table is super fast for small- to
medium-sized tables because it exists in memory only. This type of table is great for
joins and the like. The compressed table is read-only. It takes up less disk space and
is very fast. Again this is only available for paying MySQL customers with extended
email support.
Use defaults in columns—This decreases parsing time on INSERT statements and
increases performance.The next place to look for performance problems are the SQL statements that are used to manipulate
the data in your database. The following are some general guidelines to follow to help improve overall
performance:
Write your queries so they make use of indexes whenever possible. This is what
indexes are for, and they can greatly increase your database's performance.
Use the LIMIT keyword in your queries. This can sometimes force MySQL to use an
index when it normally would not.
Do not use any extraneous punctuation whenever possible, such as extra parenthesis.
MySQL will have to parse extraneous punctuation marks out before it can perform the
query. If they aren't there to begin with, MySQL can execute the query much faster.
Security is directly proportional to the amount of overhead that is created when
executing queries. The more security that is used, the more overhead that is created.
Do not sacrifice security for the sake of performance. Use security wisely and
appropriately. Some suggestions would be to create a single user for a high-traffic
database. Control security through the application and not at the server level. For
example, you could create a table that stores usernames and passwords. Have the