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. 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. 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. 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 |