O'Reilly logo

MySQL™ and JSP™ Web Applications: Data-Driven Programming Using Tomcat and MySQL by James Turner

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Database Tuning

Sometimes you can get a quick-and-easy payoff in performance with a little database tuning. For example, consider a 1,000-row table with the following schema:

CREATE TABLE DEADBEATS (
     DEBTOR    CHAR(40),
     OWES    FLOAT,
     LENDER    CHAR(40));

If you run a query asking for the count of matches between deadbeats who are also lenders, you can see that it takes quite some time:

mysql> select COUNT(*) FROM DEADBEATS LEFT JOIN DEADBEATS DB ON DEADBEATS.DEBTOR =DB.LENDER;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (8.62 sec)

Putting an index on the DEBTOR column speeds things up a bit:

 mysql> CREATE INDEX DEBTOR_INDEX ON DEADBEATS(DEBTOR); Query OK, 1000 rows affected (0.01 sec) Records: 1000 Duplicates: 0 Warnings: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required