Start by benchmarking your problem! You can take any program from the
MySQL benchmark suite (normally found in the `sql-bench'
directory) and modify it for your needs. By doing this, you can try
different solutions to your problem and test which is really the fastest
solution for you.
mysqld with the correct options. More memory gives more speed
if you have it. 10.1 Tuning server parameters.
Create indexes to make your
SELECT statements faster.
Optimize your column types to be as efficient as possible. For example,
declere columns to be
NOT NULL if possible.
10.13 How to arrange a table to be as fast/small as possible.
MySQL has two different levels of locks: Internal locks and
external locks. The internal locks ensures that all update/retrieve
operations are atomic (run without conflicts from other clients). The external
locks allows multiple MySQL servers to run on the same data and
allows you to use
isamchk to check tables without taking down
--skip-locking option disables external locking (file
locking) between SQL requests. This gives greater speed but has the
You MUST flush all tables with
mysqladmin flush-tables before
you try to check or repair tables with
tbl_name is always allowed, since that simply displays table information.)
You can't run two MySQL servers on the same data files, if
both are going to update the same tables.
--skip-locking option is on by default when compiling with
flock() isn't fully supported by MIT-pthreads on
The only case when you can't use
--skip-locking is if you run
multiple MySQL SERVERS (not clients) on the same data (or run
isamchk on the table without first flushing the
mysqld server tables
You can still use
LOCK TABLES /
UNLOCK TABLES even if you are
If updates are a problem, you can delay updates and then do many updates
in a row later. Doing many updates in a row is much quicker than doing
one at a time.
On FreeBSD systems, if the problem is with MIT-pthreads, upgrading to FreeBSD
3.0 (or higher) should help, because you can use native threads. This makes
it possible to use Unix sockets (with FreeBSD, this is quicker than
connecting using TCP/IP with MIT-pthreads) and the threads package is much
GRANT checking on the table or column level will decrease performance.
If your problem is with some explicit MySQL function, you can always
time this in the MySQL client:
mysql> select benchmark(1000000,1+1);
| benchmark(1000000,1+1) |
| 0 |
1 row in set (0.32 sec)
The above shows that MySQL can execute 1,000,000
in 0.32 seconds on a simple
All MySQL functions should be very optimized, but there may be some
exceptions and the
benchmark(loop_count,expression) is a great tool
to find if this is a problem with your query.