DB Opt

There are two ways to go about query optimization. The first way is to write more efficient queries by avoiding too many joins, not asking for columns or any other data that you don't need and keeping the number of tables down to a minimum. The second way to optimize a query is to help your database to be ready to handle specific often used queries in as fast a manner as possible. During this assignment I will explore both methods of query optimization and hopefully bring some performance gains to this database and the often used queries.

To be able to investigate in depth how to improve the queries used on this database I will select a limited number of queries, a few short and a few complex in order to produced a detailed guide to optimizing those queries. Before any optimization takes place I will benchmark each of my queries so I know exactly how long each query takes to execute and also record the initial states of each query before I make any changes in an attempt to improve query performance. I will also disable the query cache so I know my results are not skewed by MySQL's attempts to speed the query process.

Now that I have taken my few queries, benchmarked their current performance and their initial text I will look for issues that could be keeping those queries from performing like they could. One thing about this particular database is that the only indexes right now are on the primary keys. This is an obvious area for improvement and some of my fixes will involve adding a few indexes on short fields that are referenced in these queries. Simply adding an index onto every field will nullify the optimizing by choosing indexes because the index table will be just as large and slow as the unindexed tables.

One area that causes a lot of problems on poorly built MySQL queries has to do with subqueries. Subqueries actually perform multiple queries inside of a single query which steals processor power and memory space. The best alternative for subqueries is to use a standard join whenever possible, also the MySQL optimization engine can accurately tell what kind of join will best speed the process up so there is no reason to explicitly tell it to do a straight join, or any other kind of join. This database is fairly optimized in this regard, most complicated queries involve joins instead of subqueries but there is a little room for improvement that regard.

Focussing on schema design there are a couple things that should be avoided, bad index choices including multiple indexes take up room and sometimes do more bad than good. Also oftentimes we don't think about the column size when we make it an index, and when we make an index on a char(200) or bigint field we are requiring every entry in the index to be of that size. We should break apart large chunks of data into smaller more manageable, more searchable portions that will optimize our database performance.

The last way that we should optimize a database performance deals with actual server parameter changes and hardware upgrades. Most of these changes will only make a minimal impact on the performance of poorly written queries but these will help give you that extra edge on reducing query times and processor, memory usage.