Juan Pablo Espino <[EMAIL PROTECTED]> wrote on 07/08/2005 03:37:14 PM:
> Hello all! > > I have two tables in my database: results(20 000 rows) and > data_lab1(3 000 rows) Both are related by a sample number (sample_id.) > I need to find the samples of data_lab1 table that they are not in > results table. > > I think the following query is the solution: > > SELECT data_lab1.sample_id, results.sample_id > FROM data_lab1 > LEFT JOIN results ON results.sample_id = data_lab1.sample_id > WHERE results.sample_id IS NULL > > But 15 minutes later, it does not return any results and then I stop > it. I don't have a lot of experience with MySQL. My system is: > > PC: Pentium 3, 900 MHz, 512 MB-RAM > White Box Linux 3 > MySQL v4.0 > > Something wrong with the query?, another idea?, thanks in advance for > any suggestion, regards > > Juan P. Espino > My suspicion is that you do not have the right indexes to speed this up. Make sure the columns `results`.`sample_id` and `data_lab1`.`sample_id` are the both the FIRST columns in at least one index for each table (Look at the EXPLAIN plan for your query to see which indexes are or are not being used). I have a test machine (a laptop) that has several times as much data as your test but I get results in only a few seconds. >show table status; +---------------------+--------+---------+------------+--------- | Name | Engine | Version | Row_format | Rows +---------------------+--------+---------+------------+--------- | report | InnoDB | 9 | Dynamic | 206331 | sample | InnoDB | 9 | Dynamic | 173680 +---------------------+--------+---------+------------+--------- >select count(s.id) from sample s left join report r on r.sample_id = s.id where r.sample_id is null; +-------------+ | count(s.id) | +-------------+ | 1756 | +-------------+ 1 row in set (2.49 sec) You should be seeing similar response times (or faster). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
