What s Slow in MySQL Databases?

What's Slow in MySQL Databases?

Having read the preceding chapter on server optimization, we can now turn our attention to database optimization. In the next chapter, we'll look at specific query optimization.

If you have optimized your server (and perhaps thrown some hardware at the problem), you can next consider whether any of the following issues apply to you:

• Not using enough indexes. The number one cause of poor performance is using tables that have no indexes or that are without indexes on columns you are searching. This doesn't mean that you should have as many indexes as possible because that can cause the next problem on this list.

• Using too many indexes. Updating a lot of indexes as data is inserted or updated takes time. If you are retrieving data, indexes are good. When you are inserting new rows, updating rows, or deleting rows, indexes are no longer your friends . When you update data, the indexes need updating too, increasing the amount of overhead you have to deal with.

• Using table- and column-level privileges. If you use table- or column-level privileges for any resource, MySQL must check table and column privileges every time a user runs a query.

• Making the wrong database design choices. There are ways to design the actual structure of your database to make it faster to query.

We will discuss database design for speed and indexing in the next two sections.