Large MySQL database locks up when using JOIN func

Community Support question - to convert into a paid question, click here

Lines in bold below have not yet been seen by the customer - those in blue are from the customer

Username : Date : Action : Comments [ close all ]
rtaft : 06/01/03 05:43 PM : Incident created -   I have a table of Tests and Patients. The test table has a "Patient_ID" that points to the ID (primary key) of coresponding patient in the patient table. All is fine and dandy when I have 100 patients, each with 10 tests (testTable has 1,000 tests). When I have 1,000 patients with 10 tests each (10,000 tests) it works for most queries, but when joining the tables, it locks up. By locking up, I mean it sat there for over 10 minutes without responding. This is the line that freezes it: SELECT COUNT(*) FROM patientTable LEFT JOIN testTable ON (testTable.Patient_ID = patientTable.ID) WHERE some condition This works for the 1,000 tests, but not 10,000 tests. I even upgraded to MySQL 4.0.7 gamma and the same happened. MySQL does not have any public forums, and want quite a bit of money for support. Is this a bug or is my database just to big for SQL to handle?
Linegod_7611 : 07/01/03 05:24 AM : Reply received -   Copy '/usr/share/mysql/my-large.cnf' to '/etc/my.cnf' and then restart MySQL: # /etc/rc.d/init.d/mysql stop # /etc/rc.d/init.d/mysql start -------- Note: If this answer resolves your problem, please remember to close this incident.
rtaft : 04/02/03 04:21 PM : More info provided -   I ended up getting the answer from mysql's mailing lists, it was as simple as adding an index to a column.
rtaft : 04/02/03 04:21 PM : Incident closed -

---