Database Tuning

I l @ ve RuBoard

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. graphics/ccc.gif 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: 0 mysql> select COUNT(*) FROM DEADBEATS LEFT JOIN DEADBEATS DB ON DEADBEATS.DEBTOR =DB. graphics/ccc.gif LENDER; +----------+  COUNT(*)  +----------+    100000  +----------+ 1 row in set (7.73 sec) 

An index on the LENDOR column makes it go 30 times faster!

 mysql> CREATE INDEX LENDOR_INDEX ON DEADBEATS(LENDER); Query OK, 1000 rows affected (0.01 sec) Records: 1000  Duplicates: 0  Warnings: 0 mysql> select COUNT(*) FROM DEADBEATS LEFT JOIN DEADBEATS DB ON DEADBEATS.DEBTOR =DB. graphics/ccc.gif LENDER; +----------+  COUNT(*)  +----------+    100000  +----------+ 1 row in set (0.28 sec) 

Why isn't every column of every table indexed? Because when you put an index on a table, it needs to be updated on every insert or update . Too many indexes can make these operations expensive, so you can end up borrowing from Paul to pay Peter.

In addition to creating indexes, a good DBA can help you rewrite queries to be more efficient, optimize your database-configuration parameters, and recommend hardware upgrade ( especially memory or disk) that can help. You should also look to the specific documentation for your database. It should have a section (or, in the case of Oracle, entire manuals) on how to tune for best performance.

I l @ ve RuBoard


MySQL and JSP Web Applications. Data-Driven Programming Using Tomcat and MySQL
MySQL and JSP Web Applications: Data-Driven Programming Using Tomcat and MySQL
ISBN: 0672323095
EAN: 2147483647
Year: 2002
Pages: 203
Authors: James Turner

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net