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