IBM

Linuxformat.co.uk



Thespanner.co.uk

  • PHP Mysql tips
    Continuing from my earlier post on PHP performance, I thought I’d share a few Mysql tips that I’ve learnt over the years. Hope it helps someone and please leave a comment with your own tips or provide any corrections to the ones mentioned.

Informit

MySQL Query Optimization

Database management systems implement abstract concepts but do so on real hardware bound by real physical constraints. As a result, queries take time—sometimes an annoyingly long time. Find out how to minimize your wait in this sample chapter.

  • Using Indexing
    Indexing is the most important tool you have for speeding up queries. Other techniques are available to you, too, but generally the one thing that makes the most difference is the proper use of indexes. On the MySQL mailing list, people often ask for help in making a query run faster. In a surprisingly large number of cases, there are no indexes on the tables in question, and adding indexes often solves the problem immediately. It doesn’t always work like that, because optimization isn’t always simple. Nevertheless, if you don’t use indexes, in many cases you’re just wasting your time trying to improve performance by other means. Use indexing first to get the biggest performance boost and then see what other techniques might be helpful.
  • The MySQL Query Optimizer
    When you issue a query that selects rows, MySQL analyzes it to see if any optimizations can be used to process the query more quickly. In this section, we’ll look at how the query optimizer works. For additional information about optimization measures that MySQL takes, consult the optimization chapter in the MySQL Reference Manual.
  • Data Type Choices and Query Efficiency
    This section provides some guidelines about choosing data types that can help queries run more quickly
  • Loading Data Efficiently
    Most of the time you’ll probably be concerned about optimizing SELECT queries, because they are the most common type of query and because it’s not always straightforward to figure out how to optimize them. By comparison, loading data into your database is straightforward. Nevertheless, there are strategies you can use to improve the efficiency of data-loading operations. The basic principles are these
  • Scheduling and Locking Issues
    The previous sections focus primarily on making individual queries faster. MySQL also allows you to affect the scheduling priorities of statements, which may allow queries arriving from several clients to cooperate better so that individual clients aren’t locked out for a long time. Changing the priorities can also ensure that particular kinds of queries are processed more quickly.
  • Optimization for Administrators
    The previous sections describe optimizations that can be performed by unprivileged MySQL users. Administrators who have control of the MySQL server or the machine on which it runs can perform additional optimizations. For example, some server parameters pertain to query processing and may be tuned, and certain hardware configuration factors have a direct effect on query processing speed. In many cases, these optimizations improve the performance of the server as a whole, and thus have a beneficial effect for all MySQL users.

Optimizing and Tuning Your MySQL Database
Even the most well-designed database, functioning on its own or as part of an application, needs to be maintained. This hour is part of a 3-hour set of administrative lessons that will give you pointers for optimizing and tuning your database so that you get the most bang for your buck. Think of your database like a garden — basic water and sunlight will allow it to grow just fine, but a little extra plant food will help it flourish.

  • Building an Optimized Platform
    Designing a well-structured, normalized database schema is just half of the optimization puzzle. The other half is building and fine-tuning a server to run this fine database. Think about the four main components of a server: CPU, memory, hard drive, and operating system. Each of these better be up to speed, or no amount of design or programming will make your database faster!
  • MySQL Startup Options
    MySQL AB provides a wealth of information regarding the tuning of server parameters, much of which the average user will never need to use. So as not to completely overwhelm you with information, this section will contain a few of the more common startup options for a finely tuned MySQL server.
  • Optimizing Your Table Structure
    An optimized table structure is different than a well-designed table. Table structure optimization has to do with reclaiming unused space after deletions and basically cleaning up the table after structural modifications have been made.
  • Optimizing Your Queries
    Query optimization has a lot to do with the proper use of indexes. The EXPLAIN command will examine a given SELECT statement to see whether it’s optimized the best that it can be, using indexes wherever possible.
  • Summary
    Running an optimized MySQL server starts with the hardware and operating system in use. Your system’s CPU should be sufficiently fast, and you should have enough RAM in use to pick up the slack when your CPU struggles. This is especially true if MySQL shares resources with other processes, such as a Web server. Additionally, the hard drive in use is important, as a small hard drive will limit the amount of information you can store in your database. The seek time of your hard drive is important—a slow seek time will cause the overall performance of the server to be slower. Your operating system should not overwhelm your machine and should share resources with MySQL rather than using all the resources itself.



HackMySQL

Optimizing MySQL Queries
Query optimization is the first and most important task of MySQL performance. Experts agree: “[Tuning] queries (and schemas) can often give you 1000-fold performance increase” (High Performance MySQL). If you are serious about MySQL performance then you must be serious about query optimization. The following documents are detailed examples of optimizing various aspects of slow queries:

For non-technical users, Non-technical Guide to Isolating Slow MySQL Queries outlines how to get the best information to handoff to a MySQL expert who can fix the slow queries for you.

Intricacies of MySQL
If MySQL performance truly interests you then you will probably reach a point where simply knowing that something works is not enough; you need to understand the intricate details of why it works. The following documents investigate the intricate details of various MySQL issues:

DatabaseJournal

Eisabainyo

Petercooper.co.uk

Developer

Dev.MySQL

Share/Save/Bookmark

เรื่องที่เกี่ยวข้อง