Matt Thomas’ Blog » Database Optimization Project

Database Optimization Project

April 21st, 2007 | Category: Technology

Installing MySQL
We installed the MySQL Server 5.0 for Windows. We selected the Community version and downloaded the “Windows Essentials” msi file. To have the luxury of working the MySQL Query Browser, we downloaded the GUI Tools package. Once we ran the msi file, we navigated to “MySQL Server Instance Config Wizard” from the Start menu and followed the wizard to the end where we were able to set a root password (which is a pain to recover, so don’t forget it!) for administrative access to the MySQL Server. After installing the GUI Tools, we navigated to “MySQL Query Browser” from the Start menu and input ‘localhost’ as the Server Host, ‘root’ as Username, the root password, and the Default Schema we wanted as ‘mrp’. We verified that Port 3306 was input and open via our Firewall as well. The configuration looked like this:

To load up the MRP database, we ran the “MRP Schema & Data.sql” script provided by selecting File > Open Script… in the Query Browser and executing it. From here we were able to cut and paste certain queries in the “MRPQueries.doc” Word file provided and analyze them using the Query Browser. Such analysis led to optimization changes in table data types as well as the addition of helpful indices.

Recommending Indices
As a group, we viewed each table in the database and looked for opportunities for indexing. As a general rule, we created indices for tables that included a primary key and then a secondary key (another ID which type included INTEGER). If the tables had a primary key but not an evident foreign key, then we made sure that the primary key had an index. An example of the conventions we used to name the indices is as follows:

As applied to the Amenity table

There were some tables that we didn’t have a primary key but had a foreign key (e.g. unavailabledate table). For those tables we added an index for the foreign key. We did not take the time to add indexes to every table. We only indexed the tables that were associated to the queries that we were testing.

Simple Query

Amenity Table
To start our optimization process, we executed a simple query from the Amenity Table. The purpose of the query is to grabs all amenities where the groundID is greater than one. The query execution took .043 seconds without any indexes. With adding campID as an index it was resulted in a faster execute time of .0409 seconds. Although the execution time difference isn’t very significant, it taught us that indexing keys can have an impact on execution time.

SELECT * FROM Amenity WHERE groundID>’1’

More complex Queries

Camp Reservation
The first complex query that we executed pulled reservation data by stake and ward out of the Reservation and ReservationByCampsite tables. By not changing anything and running the queries as is the execution time was 1.7 seconds. In order to decrease the execution time, we deleted the casts on the startDate, endDate, and wardID attributes. After deleting the cast and changing the start date and end date type from VARCHAR[50] to DATETIME, we included actual dates in the “firstdate” and “lastdate” parameters. These changes improved the execution time to 1.6193 seconds.

We also added an index called “campID_IX” which includes the campID and reservID columns and is a generic “INDEX” kind with the binary tree index type. Adding this index improved the execution time to 1.42 seconds. Therefore, indexing and changing the data type to be more appropriate saved us 0.3 seconds of execution time. Although that may not seem significant for a small database, an enterprise database that receives thousands of queries would benefit from such an optimization.

SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
r.startDate AS arrival,
r.endDate AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE w.wardID =r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((r.startDate >= ‘2006-07-10′ AND
r.startDate <= ‘2007-04-15′) OR
(r.endDate >= ‘2006-07-11′ AND
r.endDate <= ‘2007-04-16′))
ORDER BY departure

Agents Stake
Again like before we wanted to have a benchmark time before we tried to optimize the queries which resulted in a 4.8 second execution time. As our first try at optimizing it we indexed with both columns CampID and reservID. This help reduce the execution time down to 2.5, but we thought we could still do better then that. We figured that since indices worked so well let’s keep adding more. So we have been adding indices to foreign keys and primary keys of any table that this query is referencing. Execution times were recorded for each added index. Here are the results.
• .18 seconds when stakeID on the Stake table was indexed
• .16 seconds when areaID and StakeID were indexed

In short we were able to get a query that executed at a 4.8 second time down to a .16 second execute time. This is a dramatic increase in performance and would surely save the customer from waiting a lengthy amount of time for a query to execute.

SELECT startDate, endDate, numPeople, groupType,
ward, reservedBy, r.campID,
r.contactPhone, contactAddress,
r.contactEmail, specialRequests,
managerComments, confirmed, totCost, c.name as propertyName,
managerName, c.contactPhone AS manPhone,
c.contactEmail as manEmail,
g.name AS spName, street, city, state, zip,
s.stakeName AS ssname, a.stakeName AS asname
FROM Reservation r, Camp c, AgentStake g, Stake s, Stake a
WHERE r.campID=c.campID AND c.campID=g.CampID AND
s.stakeID=stake AND a.stakeID=g.stakeID AND reservID>’100′

Select Name, Accept Reservation
Before we added indices the query ran at a speed of 1.3 seconds, but shortly after adding indices to all of the tables involved, it executed at a speed of .1415 seconds. The indices we added were the following:

• campId_IX (consisting of campId & reservID) to ‘reservation’ table
• campID_IX to ‘unavailabledate’ table
• groundID_IX (consisting of groundID & siteID) to ‘campsite’ table

We changed the firstScheduled and lastScheduled columns from VARCHAR(50) to DATETIME datatypes. We did the same for the nextfirstScheduled and nextlastScheduled columns as these also should be represented as dates. These four changes incrementally increased the speed of this query. We also changed ground_ID in the campsite table from VARCHAR[10] to INTEGER.

SELECT name, acceptReservations,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed<>’N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed=’N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID) AS Total,
(SELECT COUNT(*) FROM UnavailableDate WHERE UnavailableDate.campID=c.campID) AS Unavailable,
(SELECT COUNT(*) FROM Campsite s, Campground g WHERE s.groundID=g.groundID AND g.campID=c.campID) AS Sites,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-12′)) As Day0,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-13′)) As Day1,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-14′)) As Day2,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-15′)) As Day3,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (’2006-04-16′)) As Day4,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc
WHERE r.reservID=rfc.reservID AND r.startDate=r.endDate AND
r.campID=c.campID AND r.startDate IN (’2006-04-17′)) AS Day5
FROM Camp c
ORDER BY name

Matt Thomas
Jeff Brinkerhoff
Mike White

No comments yet. Be the first.

You must be logged in to post a comment.

Matt Thomas’ Blog Portfolio Site

Database Optimization Project

No comments yet. Be the first.

Leave a reply

Search this site

Archives

Categories

Blogroll

Meta

Matt Thomas’ BlogPortfolio Site

Database Optimization Project

No comments yet. Be the first.

Leave a reply

Search this site

Archives

Categories

Blogroll

Meta

Matt Thomas’ Blog Portfolio Site