MySQL Optimization (ii)

Vi. Optimization of applications

Should concentrate on solving the problem.

When writing an application, you should decide what is most important:

Speed

Portability between operating systems

Portability between SQL Servers

Use a persistent connection.

Cache the data in the application to reduce the load on the SQL Server.

Do not query for columns that are not needed in the application.

Do not use SELECT * FROM table_name ...

Test all parts of the application, but focus most of your effort on the overall application of the test that might be the worst possible load. By doing this in a modular way, you should be able to replace the bottleneck with a fast "dumb module", and then easily identify the next bottleneck.

If you make a lot of changes in a batch, use lock TABLES. For example, to centralize multiple updates or deletes together.

Vii. Use of portable applications

Perl DBI/DBD

Odbc

Jdbc

Python (or other language with Universal SQL Interface)

You should use only SQL constructs that exist in all the destination SQL servers or that can easily be emulated with other constructs. The Crash-me page on the www.mysql.com can help you.

Write wrappers for the operating system/sql server to provide the missing functionality.

Eight, if you need faster speed, you should:

Identify bottlenecks (CPU, disk, memory, SQL Server, operating system, API, or application) and focus on resolving them.

Use an extension that gives you faster/more flexibility.

Learn about SQL Servers so that you can use the fastest possible SQL constructs for your problems and avoid bottlenecks.

Optimizes table layouts and queries.

Use replication to get a faster choice (select) speed.

If you have a slow network connection database, use a compressed client/server protocol.

Do not be afraid when the first version of the application is not a perfect transplant, when you solve the problem, you can always optimize it later.