Thanks to all, As you says me, the solution was the indexes. I didn't have an index in results.sample_id. Now the query returns succesfully in a few seconds, regards
Juan P. Espino On 7/11/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
