will add some more speed.
When reading from tables, MySQL has to open the file that stores the table
data. How many files it keeps open at a time is defined by the
table_cache setting, which is set to 64 by default. You can increase this
setting if you have more than 64 tables, but be aware that Ubuntu imposes
limits on MySQL about how many files it can have open at a time. Going
beyond 256 is not recommended unless you have a particularly database-
heavy site and know exactly what you are doing.
The other thing you can tweak is the size of the read buffer, which is
controlled by read_buffer_size and read_buffer_rnd_size.
Both of these are allocated per connection, which means you should be very
careful to have large numbers. Whatever you choose,
read_buffer_rnd_size should be three to four times the size of
read_buffer_size, so if read_buffer_size is 1MB (suitable for
very large databases), read_buffer_rnd_size should be 4MB.
Query Optimization
The biggest speed-ups can be seen by reprogramming your SQL statements so
they are more efficient. If you follow these tips, your server will thank you:
Select as little data as possible. Rather than use SELECT *, select only
the fields you need.
If you need only a few rows, use LIMIT to select the number you need.
Declare fields as NOT NULL when creating tables to save space and
increase speed.
Provide default values for fields and use them where you can.
Be very careful with table joins because using them is the easiest way to
write inefficient queries.
If you must use joins, be sure you join on fields that are indexed. They
should also preferably be integer fields, which are faster than strings for
comparisons.
Find and fix slow queries. Add log-long-format and log-slow-
queries = /var /log/slow-queries.log to your
/etc/my.cnf file, under [mysqld], and MySQL tells you the
queries that took a long time to complete.
Use OPTIMIZE TABLE tablename to defragment tables and refresh the