Optimizing MySQL - Database Design Manual: Using MySQL for Windows - page 147

Optimizing MySQL 13

Tables Get Bigger

All of the tables that we have created during the course of this topic have had less than 10
rows. We have already talked about how MySQL is a very fast way of handling data, but
when we are using such small amounts of data there is little opportunity to test its speed.
However, if we were storing the log of a very popular website in database tables, the 30 000
hits that site may receive in a day would soon gather a huge amount of data. As soon as we
need to process large amounts of data we can introduce delays into our systems. To mini-
mize these delays and to make our query processing as fast as possible, we need to give
thought to how well our queries are written and what we can do to improve their speed.

How Well Does It Run?

In Chapter 7, we dealt with joining tables together. This can be a complex process that can
be very processor intensive on large tables. You may remember that we looked at the fol-
lowing script:

SELECT log.id as logid,

webpage.title AS pagetitle,
log.browser,
log.datecreated AS logdate,
log.ipnumber,
log.referringpage,
cookies.datecreated AS cookiecreated

FROM log, webpage, cookies

WHERE webpage.id = log.webpageid

AND cookies.cookieid=log.cookieid

If you remember that chapter, this script looked at each entry in the log table, showed the
name of the page they were looking at, and showed the first date that that cookie user
viewed the site. Figure 13.1 shows that query in action. With the limited number of rows
that we have in the tables involved, the query produces seven rows of results.
Look back at the script that generated the results in Figure 13.1. Although the results are
quite small, the script is reasonably complex. But what is actually going on behind the

151